J-Walk & Associates, Inc.

Excel 2007 VBA Challenge

Using Excel 2007, create a new workbook and add a Shape. Select the shape and use the Fill Color control on the Home tab to give the shape a theme color. Pick a color that's not in the top row of the color grid.

The challenge:

Write a VBA macro that makes the active cell exactly the same color as the shape. Importantly, the cell color must change if a new document theme is applied to the workbook (using the Themes control on the Page Layout tab).

Unless I'm missing some basic concept, the following code should do the job:

Sub GetColorFromShape()
    Dim shp As Shape
    Set shp = ActiveSheet.Shapes(1)
    With ActiveCell.Interior
       .ThemeColor = shp.Fill.ForeColor.ObjectThemeColor
       .TintAndShade = shp.Fill.ForeColor.TintAndShade
    End With
End Sub

But it doesn't. The color of the cell changes, but it's not the same shade as the shape. And sometimes, it will end with an error.

Try it in the other direction, from a cell to a shape:

Sub GetColorFromCell()
    Dim shp As Shape
    Set shp = ActiveSheet.Shapes(1)
    With shp.Fill.ForeColor
       .ObjectThemeColor = ActiveCell.Interior.ThemeColor
       .TintAndShade = ActiveCell.Interior.TintAndShade
    End With
End Sub

This doesn't work either. You'll either get an error, a shape color that's very different, or a shape color that's slightly different.

I've been playing around with this for hours, and I've come to the conclusion that it's impossible to translate colors between shapes and cells (or cells and shapes) . Prove me wrong.

Note that it's possible to transfer the exact color from the shape to the cell by using the "old" color object model:

 ActiveCell.Interior.Color = _
    ActiveSheet.Shapes(1).Fill.ForeColor.RGB

But, in this case, the color of the cell is not controlled by the document theme.

I've discovered three things:

• Excel provides 15 possible theme values for a shape, but only 12 for a range.
• The ThemeColor value of a range usually (but not always) corresponds to the ObjectThemeColor value of a shape.
• The TintAndShade property of the FillFormat object for a shape is always 0, unless you set it via code.

I wonder if this is how it was all planned, or if it's just a result of poor design?