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?

- Reader Comments -

Following are comments in response to this item.
The most recent comment is at the bottom.

1. By Jon Peltier. Comment posted 08 September, 2008 4:29am

   I think it's a combination of poor design and poor implementation.
   
   Poor design: Only eight of these colors are linked independently to the theme. The others in the palette are related to these magic eight by unvarying degree of shade and tint, and cannot be changed independently. Excel 2003 only had 56 colors, but all 56 were linked to the workbook's palette.
   
   Poor implementation: I forget the exact details, but I discovered this while editing your charts book and filed it as a bug. If you have a color which is close to 255 on the RGB scale for some combination of colors, the tint and shade setting goes awry, and for half the range of tint and shade, the resulting color is either nearly washed out or nearly saturated, without the smooth transition you might expect for this setting. This issue seems to be confined to Excel, since colleagues who use PowerPoint or Word been unable to replicate this using their own tools of choice.
2. By headtoadie. Comment posted 17 September, 2008 9:46am

   Here is another one...put a comment in a cell then run this against it. What color is the font? On mine it is pale blue, yet RGB(0,0,0) is Black.
   
   

   ActiveCell.Comment.Shape.TextFrame.Characters.Font.Color = RGB(0,0,0) 

   
   
   -HT
3. By John Walkenbach. Comment posted 17 September, 2008 10:28am

   That's weird. It's also changing the ColorIndex property, which is where the pale blue color comes from.
4. By Charley Kyd. Comment posted 25 October, 2008 2:39pm

   Two things...
   
   Thing 1:
   
   It looks like there's a bug with regard to the TintAndShade property of a shape. Specifically, this macro...
   
   Sub GetTintAndShade()
   Dim shp As Shape
   Set shp = ActiveSheet.Shapes(1)
   ActiveCell = shp.Fill.BackColor.TintAndShade
   ActiveCell.Offset(0, 1) = shp.Fill.ForeColor.TintAndShade
   End Sub
   
   ...always returns zeros for any color and any theme I assign. Of course, your results may vary.
   
   
   Thing 2...continued on next comment
   
   
   Charley Kyd
5. By Charley Kyd. Comment posted 25 October, 2008 2:40pm

   Thing 2...
   
   One significant implementation problem is that the TintAndShade value is hard-coded to the object when we assign a theme color from the palette. If a different theme uses a different TintAndShade value for the same position in the palette, the object still uses the originally assigned TintAndShade value.
   
   To illustrate, begin with the Foundry theme, which has a light color in the 10th position. Assign the colors for rows 2, 3, and 4 of column 10 (the right-most column). According to the UI, these colors are 10%, 25%, and 50% darker. Their Luminance values vary from 187 to 104.
   
   Now switch to the Verve theme, which has a dark color in the 10th position. Rows 2-4, column 10, of this theme are 80%, 60%, and 40% lighter. But our colors, set with the Foundry theme, are still 10%, 25%, and 50% darker. That is they run from very dark (Lum: 71) to nearly black (Lum: 39).
   
   Charley Kyd