Chapter 28: Working with Colors
In This Chapter
• Specifying colors in VBA code
• Using VBA conversion functions for various color models
• Converting colors to grayscale
• Working with document themes
• Modifying colors in Shape objects
• Modifying colors in charts
Specifying Colors
Dealing with color in Excel can be complicated. And often, recording a macro while you change the color of a cell or an object only adds to the confusion.
One of the most significant changes introduced in Excel 2007 was the abandonment of the old 56-color workbook palette. Back in the pre–Excel 2007 days, a workbook stored a palette of 56 colors. These colors were the only ones available for cell backgrounds, cell text, and charts. You could modify those colors, but you couldn't exceed the 56-color limit for a workbook.
The situation changed in Excel 2007, which provided access to a virtually unlimited number of colors in a workbook. (The limit is 16,777,216 colors, which I think qualifies as virtually unlimited.)
In VBA, you can specify a color as a decimal color value between 0 and 16,777,215. For example, the VBA statement that follows changes the background color of the active cell to a dark maroon:
ActiveCell.Interior.Color = 5911168
In addition, VBA has predefined constants for some common colors. For example, vbRed has a value of 255 (the decimal value for pure red), and vbGreen has a value of 65,280.
No one can keep track of nearly 17 million colors, and the predefined constants are limited. A better way to change a color is to specify the color in terms of its red, green, and blue components — the RGB color system.
The RGB color system
The RGB color system combines various levels of three colors: red, green, and blue. Each color value can range from 0 to 255. Therefore, the total number of possible colors is 256 x 256 x 256 = 16,777,216. When all three color components are 0, the color is pure black. When all three components are 255, the color is pure white. When all three are 128 (the halfway point), the color is middle gray. The remaining 16,777,213 possible combinations of these three values represent other colors.
To specify a color using the RGB system in VBA, use the RGB function. This function accepts three arguments that represent the red, blue, and green components of a color. The function returns a decimal color value.
The statement that follows uses the RGB function to assign a color that's the same as the one assigned in the preceding section (that dark maroon, 5911168):
ActiveCell.Interior.Color = RGB(128, 50, 90)
Table 28-1 shows the RGB values and the decimal color code of some common colors.
The HSL color system
If you select the More Colors option when choosing a color in Excel, you see the Colors dialog box. Click the Custom tab, and you can choose from two color models to specify your color: RGB and HSL. Figure 28-1 shows the Colors dialog box with the HSL color model selected.
Figure 28-1: Choosing a color using the HSL color system.
In the HSL color system, colors are specified using three parameters: Hue, Saturation, and Luminance. As with RGB colors, each parameter can range from 0 to 255. Each RGB color has an equivalent HSL color, and each HSL color has an equivalent decimal color value. In other words, you can specify any of the 16,777,216 colors by using any of the three color systems: RGB, HSL, or decimal.
The Colors dialog box is the only area in which Excel supports the HSL color model. For example, when you specify a color using VBA, it must be a decimal color value. You can use the RGB function to return a decimal color value. However, VBA doesn't have a function that allows you to specify a color in terms of hue, saturation, and luminance.
A function that returns a color value
If you need to let the user select a color, check out the following useful function. The GetAColor function displays the Colors dialog box (refer to Figure 28-1) and returns a decimal color value (or False, if the dialog box is canceled).
Function GetAColor() As Variant
Dim OldColor As Double
OldColor = ActiveWorkbook.Colors(1)
If Application.Dialogs(xlDialogEditColor).Show(1) = True Then
GetAColor = ActiveWorkbook.Colors(1)
Else
GetAColor = False
End If
ActiveWorkbook.Colors(1) = OldColor
End Function
The function takes advantage of the old 56-color palette. It's the same dialog box that would appear if the user were replacing the first color in the palette. However, the palette doesn't get changed, and the function returns the value of the color selected.
This example is available on the book's website in the get a color function.xlsm file.
Converting colors
If you know a color's red, green, and blue component values, converting the color to a decimal color is easy. Just use the VBA RGB function. Assume three variables (r, g, and b), each of which represents a color component value between 0 and 255. To calculate the equivalent decimal color value, use a statement like this:
DecimalColor = RGB(r, g, b)
To perform this conversion in a worksheet formula, create this simple VBA wrapper function:
Function RGB2DECIMAL(R, G, B) As Long
‘ Converts from RGB to decimal color
RGB2DECIMAL = RGB(R, G, B)
End Function
The following example worksheet formula assumes that the three color values are in A1:C1:
=RGB2DECIMAL(A1,B1,C1)
Converting a decimal color to its red, green, and blue components is more complicated. Here's a function that returns a three-element array:
Function DECIMAL2RGB(ColorVal) As Variant
‘ Converts a color value to an RGB triplet
‘ Returns a 3-element variant array
DECIMAL2RGB = Array(ColorVal 256 ^ 0 And 255, _
ColorVal 256 ^ 1 And 255, ColorVal 256 ^ 2 And 255)
End Function
To use the DECIMAL2RGB function in a worksheet formula, the formula must be entered as a three-cell array formula. For example, assume that cell A1 contains a decimal color value. To convert that color value to its RGB components, select a three-cell horizontal range and then enter the following formula. Press Ctrl+Shift+Enter to make it an array formula and don't enter the braces.
{=DECIMAL2RGB(A1)}
If the three-cell range is vertical, you need to transpose the array, as follows:
{=TRANSPOSE(DECIMAL2RGB(A1))}
Figure 28-2 shows the DECIMAL2RGB and DECIMAL2HSL functions in use in a worksheet.
The book's website contains a workbook with the following color conversion functions: DECIMAL2RGB, DECIMAL2HSL, HSL2RGB, RGB2DECIMAL, RGB2HSL, and HSL2DECIMAL. The file is named color conversion functions.xlsm.
Figure 28-2: A worksheet that uses the DECIMAL2RGB and DECIMAL2HSL functions.
More about decimal color values
You may be curious about how the 16,777,216 decimal color values are arranged. Color 0 is black and color 16,777,216 is white, but what about all the colors in between?
It might help to think of the decimal color values as being generated by nested For-Next loops, as shown in the following code:
Sub GenerateColorValues()
Dim Red As Long, Blue As Long, Green As Long
Dim AllColors(0 To 16777215) As Long
Dim ColorNum As Long
ColorNum = 0
For Blue = 0 To 255
For Green = 0 To 255
For Red = 0 To 255
AllColors(ColorNum) = RGB(Red, Blue, Green)
ColorNum = ColorNum + 1
Next Red
Next Green
Next Blue
End Sub
After this procedure runs, the values in the AllColors array correspond to the decimal color values used by Excel.
Understanding Grayscale
When you create worksheets and charts that are intended to be printed, it's important to remember that not everyone has a color printer. And even if your chart is printed on a color printer, it's possible that it may be photocopied or faxed, or viewed by someone who is color-blind (a condition that affects about 8 percent of the male population).
When content is printed on a noncolor device, colors are converted to grayscale. Sometimes you'll be lucky, and your colors will display nicely when converted to grayscale. Other times, you won't be so lucky. For example, the columns in a chart may be indistinguishable when the colors are converted.
Every grayscale color has an equal component of red, green, and blue. Pure black is RGB(0, 0, 0). Pure white is RGB(255, 255, 255). Neutral gray is RGB(128, 128, 128). Using this color system produces 256 shades of gray.
To create a 256-color grayscale in a range of cells, execute the procedure that follows. It colors the background of cells in the range A1:A256, starting with black and ending with white. The result is a smooth gradient. You might want to zoom out on the worksheet to see the entire range.
Sub GenerateGrayScale()
Dim r As Long
For r = 0 To 255
Cells(r + 1, 1).Interior.Color = RGB(r, r, r)
Next r
End Sub
Figure 28-3 shows the result, after decreasing the row heights and making column A wider.
Figure 28-3: Cells displaying 256 shades of gray.
Converting colors to gray
One approach to grayscale conversion is to simply average the red, green, and blue components of a color and use that single value for the red, green, and blue components of its grayscale equivalent. That method, however, doesn't take into account the fact that different colors are perceived as varying levels of brightness. For example, green is perceived to be brighter than red, and red is perceived to be brighter than blue.
Perceptual experiments have arrived at the following recipe to convert an RGB color value to an approximate grayscale value:
• 29.9 percent of the red component
• 58.7 percent of the green component
• 11.4 percent of the blue component
For example, consider color value 16751001, a shade of violet that corresponds to RGB(153, 153, 255). Applying the factors listed previously, the RGB values (rounded) are
• Red: 29.9 percent × 153 = 46
• Green: 58.7 percent × 153 = 90
• Blue: 11.4 percent × 255 = 29
The sum of these values is 165. Therefore, the corresponding grayscale RGB value for color value 16751001 is RGB(165, 165, 165).
Following is a VBA function that accepts a decimal color value as its argument and returns the corresponding grayscale decimal value:
Function Grayscale(color) As Long
Dim r As Long, g As Long, b As Long
r = (color 256 ^ 0 And 255) * 0.299
g = (color 256 ^ 1 And 255) * 0.587
b = (color 256 ^ 2 And 255) * 0.114
Grayscale = RGB(r + g + b, r + g + b, r + g + b)
End Function
Experimenting with Colors
Figure 28-4 shows a workbook that I created that deals with colors. If you're at all confused about how the RGB color model works, spend some time with this color demo workbook.
Figure 28-4: This workbook demonstrates how red, green, and blue colors combine.
This workbook, named RGB color demo.xlsm, is available on the book's website.
This workbook contains three vertical scroll bars, each of which controls the background color of a range. Use these scroll bars to specify the red, green, and blue components for a color to values between 0 and 255. Moving the scroll bars changes several areas of the worksheet:
• The cells above the scroll bars display the color components in hexadecimal (00–FF) and in decimal (0–255). Hexadecimal RGB color values are often used in specifying colors for HTML documents.
• The ranges next to each scroll bar change intensity, corresponding to the scroll bar's position (that is, the value of the color component).
• A range below the scroll bars depicts the combined color, determined by the RGB values you specify.
• A cell displays the decimal color value.
• Another range depicts the color's approximate appearance when it's converted to grayscale.
• A range of cells shows the corresponding HSL color values.
Understanding Document Themes
A significant feature introduced in Excel 2007 was 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 2013 ships with many document themes, and you can also download or create additional themes. The 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. If you apply a different theme to the document, the document changes to reflect the new theme's colors, fonts, and effects.
If you haven't explored document themes, open the workbook named document theme demo.xlsx found on the book's website. This workbook contains a range that shows each theme color, two shapes, text (using the headings and body fonts), and a chart. Choose Page Layout⇒Themes⇒Themes Gallery to see how the worksheet changes with each theme.
Users can also mix and match theme elements. For example, you can 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. You can save these customized themes and then apply them to other workbooks.
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 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 an object, the color is selected from a control like the one shown in Figure 28-5. 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.
Figure 28-5: A color selection control.
Excel objects (such as ranges, shapes, and chart elements) can be colored in two ways: by applying a theme color or by applying a standard color. When you use a color selection control, you apply a theme color by clicking a color in the Theme Colors section. If you choose a color from the Standard Colors section (or if you click More Colors), the color is not a theme color and will not change if you change the document theme.
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, Lighter 80%.” The tooltip also displays a color name, which varies depending on the theme.
The first row in each column of colors contains the pure color. Below each pure color are six tint and shade variations. Table 28-2 shows the color descriptions for the color picker controls.
Keep in mind that these color names remain the same, even if a different document theme is applied. The document theme colors consist of the ten colors displayed in the top row (four text/background colors and six accent colors), and each of these ten colors has five tint/shade variations. If you select Page Layout⇒Themes⇒Colors⇒Create New Theme Colors, you'll see that a theme has two additional colors: Hyperlink and Followed Hyperlink. These are the colors applied when a hyperlink is created, and they are not shown in the color selection control.
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, Darker 25%,” and for the text color, I chose “Text 2, Lighter 80%.”
Sub ChangeColors()
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.799981688894314
End With
End Sub
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 statements are included to maintain any pattern that may exist in the range.
The recorded macro, after I deleted the three pattern-related properties and added comments, is
Sub ChangeColors()
With Selection.Interior
‘(Accent 2, Darker 25%)
.ThemeColor = xlThemeColorAccent2
.TintAndShade = -0.249977111117893
End With
With Selection.Font
‘(Text 2, Lighter 80%)
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.799981688894314
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. Property values are assigned using built-in constants, and these values correspond to the column number of the 10 x 6 theme color table. For example, xlThemColorAccent2 has a value of 6. 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. The TintAndShade value corresponds to the color name displayed in the color selection controls.
If the color variation is expressed as “Darker,” the TintAndShade property value is negative. If the color variation is expressed as “Lighter,” the TintAndShade property value is positive.
Note that the actual colors are not specified. The colors applied depend on the document theme.
I don't know why the TintAndShade values have such a high level of precision in recorded macros. It's certainly not necessary. For example, a TintAndShade property value of –0.249977111117893 produces the same visual result as a TintAndShade property value of –0.25.
For a demonstration of how the TintAndShade property changes a color, open the tintandshade demo.xlsm workbook on the book's website (see Figure 28-6). Specify a starting color, and the macro displays that color with 50 levels of the TintAndShade property values, ranging from –1 to +1. It also displays the decimal color value and the red, green, and blue components of the color (which are displayed in a chart).
Figure 28-6: This workbook demonstrates how the TintAndShade property affects a color.
Displaying all theme colors
I wrote a macro that displays all 60 theme color variations in a range of cells. These are the 60 colors that appear in the color selection controls.
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 28-7 shows the result of executing the ShowThemeColors procedure. (It looks better in color.) If you switch to a different document theme, the colors will be updated to reflect those in the new theme.
This example, named generate theme colors.xlsm, is available on the book's website.
Earlier in this chapter, I 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 task easier. The following 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)
Figure 28-7: A VBA macro generated these theme colors.
It's important to understand that assigning a color in this way doesn't make it a theme color. In other words, if the user switches to a new document theme, range A1:F24 won't change colors. To change cell colors in a way that is consistent with themes, you must use the ThemeColor property and (optionally) the TintAndShade property.
Working with Shape Objects
So far, this chapter has focused exclusively on modifying the color of a range. This section provides examples of changing colors in Shape objects. In Excel, use the Insert⇒Illustrations⇒Shapes group to add a shape to a worksheet.
Figure 28-8 shows a shape inserted in a worksheet. This object's default name is Right Arrow 1. The number in the name varies, depending on how many shapes you have inserted. For example, if you had previously inserted two other shapes (of any style), the name would be Right Arrow 3.
Figure 28-8: A Shape object on a worksheet.
A shape's background color
The background color of a Shape object is determined by the RGB property. So, to get the decimal color value of this shape, use a statement like this:
MsgBox ActiveSheet.Shapes(“Right Arrow 1”).Fill.ForeColor.RGB
This statement may be a bit confusing, so I'll break it down. The Fill property of the Shape object returns a FillFormat object. The ForeColor property of the FillFormat object returns a ColorFormat object. So the RGB property actually applies to the ColorFormat object, and this property contains the decimal color value.
If you're confused about the use of the ForeColor property in this example, you're not alone. Most people, myself included, would expect to use the BackColor property of the FillFormat object to change the background color of an object. As it turns out, the BackColor property is used for the second color if the object is shaded or filled with a pattern. For an unfilled Shape with no pattern, the ForeColor property controls the background color.
When working with Shape objects, you almost always want your code to perform multiple actions. Therefore, it's efficient to create an object variable. The code that follows creates an object variable named Shp:
Dim Shp As Shape
Set Shp = ActiveSheet.Shapes(“Right Arrow 1”)
MsgBox Shp.Fill.ForeColor.RGB
An additional advantage to creating an object variable is that you can take advantage of the VBE Auto List Members feature, which displays the possible properties and objects as you type (see Figure 28-9). This feature is particularly helpful in the case of Shape objects because some actions you take with Shapes are recorded by Excel's macro recorder.
If you'll be working only with the shape's colors, you can create an object variable for the shape's ColorFormat object, like this:
Dim ShpCF As ColorFormat
Set ShpCF = ActiveSheet.Shapes(“Right Arrow 1”).Fill.ForeColor
MsgBox ShpCF.RGB
Figure 28-9: Typing a statement with the assistance of the Auto List Members feature.
The RGB property of the ColorFormat object controls the color of the shape. Following are some additional properties. If you're not familiar with document theme colors, see the section “Understanding document theme colors,” earlier in this chapter.
• Brightness: A number between –1 and +1 that represents the luminosity of the color. A value of –1 makes the color black, and a value of +1 makes the color white.
• ObjectThemeColor: A number between 1 and 15 that represents the theme color.
• SchemeColor: A number that ranges from 0 to 80 that represents the color as an index in the current color scheme. These are colors from the old 56-color palette, and I don't see any need to use the SchemeColor property.
• TintAndShade: A number between –1 and +1 that represents the darkness or lightness of the theme color.
• Type: A number that represents the ColorFormat object type. As far as I can tell, this read-only property is always 1, which represents the RGB color system.
Changing the background color of a shape doesn't affect the shape's outline color. To modify the color of a shape's outline, access the ColorFormat object of the shape's LineFormat object. The following statements set a Shape's background color and outline to red:
Dim Shp As Shape
Set Shp = ActiveSheet.Shapes(“Right Arrow 1”)
Shp.Fill.ForeColor.RGB = RGB(255, 0, 0)
Shp.Line.ForeColor.RGB = RGB(255, 0, 0)
Here's an alternative way to accomplish the same effect, using object variables:
Dim Shp As Shape
Dim FillCF As ColorFormat
Dim LineCF As ColorFormat
Set Shp = ActiveSheet.Shapes(“Right Arrow 1”)
Set FillCF = Shp.Fill.ForeColor
Set LineCF = Shp.Line.ForeColor
FillCF.RGB = RGB(255, 0, 0)
LineCF.RGB = RGB(255, 0, 0)
Keep in mind that the preceding code does not produce colors that are compatible with document themes. To specify theme-compatible colors, you must use the ObjectThemeColor property and (optionally) the TintAndShade property.
Shapes and theme colors
To apply theme colors to a shape, you use the ObjectThemeColor, TintAndShade and Brightness properties of the shape's Forecolor object. I recorded a macro while setting a shape's color to “Accent 4, Lighter 40%.” It produced this code:
ActiveSheet.Shapes.Range(Array(“Right Arrow 1”)).Select
With Selection.ShapeRange.Fill
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorAccent4
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0.400000006
.Transparency = 0
.Solid
End With
Note that the macro adjusts the Brightness property, not the TintAndShade property. I discovered that the Brightness property for a shape corresponds to the TintAndShade property for a cell. The Brightness property was introduced in Excel 2010, so using this property will generate an error in Excel 2007.
Unfortunately, Microsoft's implementation of document themes is seriously flawed. For example, cell theme colors don't always match up with shape theme colors. Cell theme colors range from 1 to 12, and theme colors for objects range from 1 to 15. The first four numbers don't match.
I wrote a macro that makes a shape the same color as cell A1, but the macro is not as simple as it should be.
Sub ColorShapeLikeCell()
‘ Make a shape's color match cell A1's color
Dim Cell As Interior
Dim Shape As ColorFormat
Set Cell = Range(“A1”).Interior
Set Shape = ActiveSheet.Shapes(1).Fill.ForeColor
If Cell.ThemeColor = 0 Then
Shape.RGB = Cell.Color
Else
Select Case Cell.ThemeColor
Case 1: Shape.ObjectThemeColor = 2
Case 2: Shape.ObjectThemeColor = 1
Case 3: Shape.ObjectThemeColor = 4
Case 4: Shape.ObjectThemeColor = 3
Case Else
Shape.ObjectThemeColor = Cell.ThemeColor
End Select
Shape.Brightness = Cell.TintAndShade
End If
End Sub
If the ThemeColor property is 0, the cell's color is not a theme color. In such a case, the shape gets the same color. If the cell uses a theme color, the code needs to adjust if the theme color is 1, 2, 3, or 4. Also, the shape's Brightness property is set to the cell's TintAndShade value.
And here's the complementary macro, ColorCellLikeShape. This macro makes cell A1 the same color as a shape.
Sub ColorCellLikeShape()
‘ Make cell A1 color match a shape's color
Dim Cell As Interior
Dim Shape As ColorFormat
Set Cell = Range(“A1”).Interior
Set Shape = ActiveSheet.Shapes(1).Fill.ForeColor
If Shape.ObjectThemeColor = 0 Then
Cell.Color = Shape.RGB
Else
Select Case Shape.ObjectThemeColor
Case 1: Cell.ThemeColor = 2
Case 2: Cell.ThemeColor = 1
Case 3: Cell.ThemeColor = 4
Case 4: Cell.ThemeColor = 3
Case Else
Cell.ThemeColor = Shape.ObjectThemeColor
End Select
Cell.TintAndShade = Shape.Brightness
End If
End Sub
A workbook that contains these two procedures is available on the book's website in the matching colors.xlsm file.
Modifying Chart Colors
This section describes how to change colors in a chart. The most important point is to identify the specific chart element that you want to modify. In other words, you need to identify the object and then set the appropriate properties.
Figure 28-10 shows a simple column chart named Chart 1. This chart has two data series, a legend, and a chart title.
Figure 28-10: A simple column chart.
Following is a VBA statement that changes the color of the first data series to red:
ActiveSheet.ChartObjects(“Chart 1”).Chart. _
SeriesCollection(1).Format.Fill.ForeColor.RGB = vbRed
To the uninitiated, this statement is probably confusing because so many objects are involved. The object hierarchy is as follows.
The active sheet contains a ChartObjects collection. One object in that collection is the ChartObject named Chart 1. The Chart property of the ChartObject object returns a Chart object. The Chart object has a SeriesCollection collection, and one Series object in the collection has an index number of 1. The Format property of the Series object returns a ChartFormat object. The Fill property of the ChartFormat object returns a FillFormat object. The ForeColor property of the FillFormat object returns a ColorFormat object. The RGB property of the ColorFormat object is set to red.
Refer to Chapter 16 for more information about using VBA to work with charts.
Another way of writing the preceding statement, using object variables to identify the individual objects (and, perhaps, clarify the objects' relationships), is
Sub ChangeSeries1Color
Dim MyChartObject As ChartObject
Dim MyChart As Chart
Dim MySeries As Series
Dim MyChartFormat As ChartFormat
Dim MyFillFormat As FillFormat
Dim MyColorFormat As ColorFormat
‘ Create the objects
Set MyChartObject = ActiveSheet.ChartObjects(“Chart 1”)
Set MyChart = MyChartObject.Chart
Set MySeries = MyChart.SeriesCollection(1)
Set MyChartFormat = MySeries.Format
Set MyFillFormat = MyChartFormat.Fill
Set MyColorFormat = MyFillFormat.ForeColor
‘ Change the color
MyColorFormat.RGB = vbRed
End Sub
The RGB property accepts a decimal color value, which I specified using a built-in VBA constant.
Other color-related properties of the ColorFormat object are the same as for shapes (described earlier in this chapter). The following properties are relevant to document theme colors:
• Brightness: A number between –1 and +1 that represents the luminosity of the color. A value of –1 makes the color black, and a value of +1 makes the color white.
• ObjectThemeColor: A number between 1 and 15 that represents the theme color.
• TintAndShade: A number between –1 and +1 that represents the darkness or lightness of the theme color.
The examples in this section are available on the book's website in the chart colors.xlsm file.
If you use VBA to set a theme color for any element of a chart, the color will be applied but will not change if the user switches to a different document theme. When your code sets the ObjectThemeColor property value, it always reverts to zero — which means that the color is not controlled by document themes. This long-standing bug has not been corrected in Excel 2013.
You can also specify color gradients. Here's an example that applies a preset gradient to the second data series in a chart. Note that the gradient is set using the FillFormat object:
Sub AddPresetGradient()
Dim MyChart As Chart
Set MyChart = ActiveSheet.ChartObjects(“Chart 1”).Chart
With MyChart.SeriesCollection(1).Format.Fill
.PresetGradient _
Style:=msoGradientHorizontal, _
Variant:=1, _
PresetGradientType:=msoGradientFire
End With
End Sub
Working with other chart elements is similar. The procedure that follows changes the colors of the chart's chart area and plot area, using colors from the current document theme:
Sub RecolorChartAndPlotArea()
Dim MyChart As Chart
Set MyChart = ActiveSheet.ChartObjects(“Chart 1”).Chart
With MyChart
.ChartArea.Format.Fill.ForeColor.ObjectThemeColor = _
msoThemeColorAccent6
.ChartArea.Format.Fill.ForeColor.TintAndShade = 0.9
.PlotArea.Format.Fill.ForeColor.ObjectThemeColor = _
msoThemeColorAccent6
.PlotArea.Format.Fill.ForeColor.TintAndShade = 0.5
End With
End Sub
The final example in this section applies a random color to each chart element. Using this macro virtually guarantees an ugly chart. However, this code demonstrates how to change the color for other chart elements. The UseRandomColors procedure uses a simple function, RandomColor, to determine the color used.
Sub UseRandomColors()
Dim MyChart As Chart
Set MyChart = ActiveSheet.ChartObjects(“Chart 4”).Chart
With MyChart
.ChartArea.Format.Fill.ForeColor.RGB = RandomColor
.PlotArea.Format.Fill.ForeColor.RGB = RandomColor
.SeriesCollection(1).Format.Fill.ForeColor.RGB = RandomColor
.SeriesCollection(2).Format.Fill.ForeColor.RGB = RandomColor
.Legend.Font.Color = RandomColor
.ChartTitle.Font.Color = RandomColor
.Axes(xlValue).MajorGridlines.Border.Color = RandomColor
.Axes(xlValue).TickLabels.Font.Color = RandomColor
.Axes(xlValue).Border.Color = RandomColor
.Axes(xlCategory).TickLabels.Font.Color = RandomColor
.Axes(xlCategory).Border.Color = RandomColor
End With
End Sub
Function RandomColor()
RandomColor = Application.RandBetween(0, RGB(255, 255, 255))
End Function