Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)
A significant new feature in Excel 2007 is document themes. With a single mouse click, the user can change the entire look of a document. A document theme consists of three components : colors, fonts, and effects (for graphic objects). The rationale for using themes is that they may help users produce better-looking and more consistent documents. A theme applies to the entire workbook, not just the active worksheet.
About document themes
Microsoft Office 2007 ships with 20 document themes, and additional themes can be added. The user interface Ribbon includes several style galleries (for example, the Chart Styles gallery). The styles available in these galleries vary depending on which theme is assigned to the document. And, if you apply a different theme to the document, the document changes to reflect the new theme's colors, fonts, and effects.
| CD-ROM | If you haven't explored document themes, open the workbook named |
Users can also mix and match theme elements. For example, it's possible to use the colors from one theme, the fonts from another theme, and the effects from yet a different theme. In addition, the user can create a new color set or a new font set. These customized themes can be saved and then applied to other workbooks.
| Note | The concept of document themes is based on the notion that users will apply little, if any, non-theme formatting to the document. If the user applies colors or fonts that aren't part of the current theme, this formatting will not be modified if a new theme is applied to the document. Therefore, it's still very easy to create an ugly document with mismatched colors and too many different fonts. |
Understanding document theme colors
When a user applies a color to a cell or object, the color is selected from a control like the one shown in Figure 30-4. The control displays the 60 theme colors (10 columns by 6 rows) plus 10 additional standard colors. Clicking the More Colors option displays the Color dialog box, in which the user can specify any of the 16,777,216 available colors.
The 60 theme colors are identified by pop-up ToolTips. For example, the color in the second row of the sixth column is known as " Accent 2, Tint 20%." Table 30-2 shows the names of all 60 theme colors. Examine the table, and you see that there isn't much of a pattern - although the Accent colors are consistent in terms of the tint and shade variations. Also, note that some color variations are known as a tint and others are known as a shade .
| Row/Column | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Text/ | Text/ | Text/ | Text/ | Accent | Accent | Accent | Accent | Accent | Accent |
| Background | Background | Background | Background | 1 | 2 | 3 | 4 | 5 | 6 | |
| 1 | 2 | 3 | 4 | |||||||
| 2 | Shade | Tint | Shade | Tint | Tint | Tint | Tint | Tint | Tint | Tint |
| 95% | 95% | 90% | 20% | 20% | 20% | 20% | 20% | 20% | 20% | |
| 3 | Shade | Tint | Shade | Tint | Tint | Tint | Tint | Tint | Tint | Tint |
| 85% | 65% | 75% | 40% | 40% | 40% | 40% | 40% | 40% | 40% | |
| 4 | Shade | Tint | Shade | Tint | Tint | Tint | Tint | Tint | Tint | Tint |
| 75% | 75% | 50% | 60% | 60% | 60% | 60% | 60% | 60% | 60% | |
| 5 | Shade | Tint | Shade | Shade | Shade | Shade | Shade | Shade | Shade | Shade |
| 65% | 85% | 25% | 75% | 75% | 75% | 75% | 75% | 75% | 75% | |
| 6 | Shade | Tint | Shade | Shade | Shade | Shade | Shade | Shade | Shade | Shade |
| 50% | 95% | 10% | 50% | 50% | 50% | 50% | 50% | 50% | 50% |
Keep in mind that these color names remain the same, even if a different document theme is applied. The document theme colors actually consist of the 10 colors displayed in the top row (four text/background colors and six accent colors), and each of these 10 colors has five tint/shade variations.
| Note | If you select Page Layout |
You may find it enlightening to record a macro while you change the fill color and text color of a range. Following is a macro that I recorded when a range was selected. For the fill color, I chose "Accent 2, Shade 75%," and for the text color, I chose "Text/Background 3, Shade 90%."
Sub Macro1() With Selection.Interior .Pattern = xlSolid .PatternColorIndex = 56 .ThemeColor = 6 .TintAndShade = -0.249977111117893 .PatternTintAndShade = 0 End With With Selection.Font .ThemeColor = 3 .TintAndShade = -9.99786370433668E-02 End With End Sub
First of all, you can safely ignore the three pattern- related properties ( Pattern , PatternColorIndex , and PatternTintAndShade ). These properties refer to the ugly, old-fashioned (but still supported) cell patterns, which you can specify in the Fill tab of the Format Cells dialog box. These properties simply maintain any existing pattern that may exist in the range.
The recorded macro, after I delete the three pattern-related properties, is
Sub Macro1() With Selection.Interior .ThemeColor = 6 .TintAndShade = -0.249977111117893 End With With Selection.Font .ThemeColor = 3 .TintAndShade = -9.99786370433668E-02 End With End Sub
As you can see, each color is specified in terms of a ThemeColor property and a TintAndShade property. The ThemeColor property is easy enough to decipher. It's simply the column number of the 10-x-6 theme color table. But what about the TintAndShade property?
The TintAndShade property can have a value between “1 and +1. A value of “1 results in black, and a value of +1 results in white. A TintAndShade property value of 0 gives the pure color. In other words, as the TintAndShade value goes negative, the color gets increasingly darker until it's pure black. As the TintAndShade value goes positive, the color gets increasingly lighter until it's pure white.
To arrive at the TintAndShade property value that corresponds to a particular theme color variation, look at Table 30-2.
-
If the color variation is expressed as a Tint, the TintAndShade property value is 1 minus the percent value (a positive value, making the variation lighter than the original color).
-
If the color variation is expressed as a Shade, the TintAndShade property value is the percent value minus 1 (a negative value, making the variation darker than the original value).
| CD-ROM | For a demonstration of how the TintAndShade property changes a color, open the |
Displaying all theme colors
Using the information in Table 30-1, I wrote a macro that displays all 60 theme color variations in a range of cells.
Sub ShowThemeColors() Dim r As Long, c As Long For r = 1 To 6 For c = 1 To 10 With Cells(r, c).Interior .ThemeColor = c Select Case c Case 1 'Text/Background 1 Select Case r Case 1: .TintAndShade = 0 Case 2: .TintAndShade = -0.05 Case 3: .TintAndShade = -0.15 Case 4: .TintAndShade = -0.25 Case 5: .TintAndShade = -0.35 Case 6: .TintAndShade = -0.5 End Select Case 2 'Text/Background 2 Select Case r Case 1: .TintAndShade = 0 Case 2: .TintAndShade = 0.5 Case 3: .TintAndShade = 0.35 Case 4: .TintAndShade = 0.25 Case 5: .TintAndShade = 0.15 Case 6: .TintAndShade = 0.05 End Select Case 3 'Text/Background 3 Select Case r Case 1: .TintAndShade = 0 Case 2: .TintAndShade = -0.1 Case 3: .TintAndShade = -0.25 Case 4: .TintAndShade = -0.5 Case 5: .TintAndShade = -0.75 Case 6: .TintAndShade = -0.9 End Select Case Else 'Text/Background 4, and Accent 1-6 Select Case r Case 1: .TintAndShade = 0 Case 2: .TintAndShade = 0.8 Case 3: .TintAndShade = 0.6 Case 4: .TintAndShade = 0.4 Case 5: .TintAndShade = -0.25 Case 6: .TintAndShade = -0.5 End Select End Select Cells(r, c) = .TintAndShade End With Next c Next r End Sub
Figure 30-6 shows the result of executing the ShowThemeColors procedure (it looks better in color). If you change to a different document theme, the colors will be updated to reflect those in the new theme.
| CD-ROM | This example, named |
So far in this chapter, I've described how to change the fill color of a range by setting the Color property of the Interior object. As I noted, using the VBA RGB function makes this easier. These two statements demonstrate how to change the fill color of a range (they both have the same result):
Range("A1:F24").Interior.Color = 5913728 Range("A1:F24").Interior.Color = RGB(128, 60, 90)
What if you'd like your code to change the background color of a range to a specific theme color, such as the color in the third row of the sixth column (the color identified as "Accent 2, Tint 40%")?
Unfortunately, the Excel 2007 designers seemed to have forgotten to include a direct way to specify a theme color using this type of indexing. You might think the ColorIndex property would do the job, but it doesn't. The ColorIndex property refers to colors in the (pre “Excel 2007) 56-color palette.
In actual practice, this omission is not a serious problem. When setting a color, the important property is the ThemeColor property, which ranges from 1 to 10. Your code can assign a value to the TintAndShade property to vary that color (a negative value for a darker variation, a positive value for a lighter variation). If the user applies a different document theme, the color still changes in a relative manner.