Chapter 30: 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 2010 is no trivial matter. I'm the first to admit that it can be complicated. And often, recording a macro while you change the color of a cell or 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 any or all of those colors, but there was no way to exceed the 56-color limit for a workbook.

But things changed with the introduction of Excel 2007. You now have access to a virtually unlimited number of colors in a workbook — actually, the limit is 16,777,216 colors, but that certainly qualifies as virtually unlimited in my book.

In VBA, you can specify a color as a decimal color value, which is a number 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, of course, 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 of these color values can range from 0 through 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 half-way 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 exactly the same as the one assigned in the preceding section (that dark maroon, 5911168):

ActiveCell.Interior.Color = RGB(128, 50, 90)

Table 30-1 shows the RGB values and the decimal color code of some common colors.

Table 30-1: Color Examples

Name	Red Component	Green Component	Blue Component	Color Value
Black	0	0	0	0
White	255	255	255	16777215
Red	255	0	0	255
Green	0	255	0	65280
Blue	0	0	255	16711680
Yellow	255	255	0	65535
Pink	255	0	255	16711935
Turquoise	0	255	255	16776960
Brown	153	51	0	13209
Indigo	51	51	153	10040115
80% Gray	51	51	51	3355443

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 30-1 shows the Colors dialog box with the HSL color model selected.

FIGURE 30-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 of these parameters 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.

Although the Colors dialog box lets you specify a color using the HSL color model, this is actually 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, of course, 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.

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 VBA's 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 the three color values are in A1:C1:

=RGB2DECIMAL(A1,B1,C1)

Converting a decimal color to its red, green, and blue components is a bit 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 30-2 shows the DECIMAL2RGB and DECIMAL2HSL functions in use in a worksheet.

The companion CD-ROM 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 30-2: A worksheet that uses the DECIMAL2RGB and DECIMAL2HSL functions

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, 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. 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 30-3 shows the result, after decreasing the row heights and making column A wider.

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 a grayscale value

• 28.7% of the red component

• 58.9% of the green component

• 11.4% of the blue component

FIGURE 30-3: Cells displaying 256 shades of gray.

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 are

• Red: 28.7% × 153 = 44

• Green: 58.9% × 153 = 90

• Blue: 11.4% × 255 = 29

The sum of these values is 163. Therefore, the corresponding grayscale RGB value for color value 16751001 is RGB(163, 163, 163).

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.287

g = (color 256 ^ 1 And 255) * 0.589

b = (color 256 ^ 2 And 255) * 0.114

Grayscale = RGB(r + g + b, r + g + b, r + g + b)

End Function

Viewing charts as grayscale

Unfortunately, Excel's print preview feature doesn't do grayscale conversion. For example, if you have a black and white laser printer, previewing your print job shows colors — not the grayscale that is actually produced by your printers.

The Sheet tab of the Page Setup dialog box (displayed by clicking the dialog box launcher in the Page Layout⇒Page Setup group), has an option labeled Black And White. When checked, your charts are printed in true black and white, not grayscale. Colors are converted to patterns that consist of black and white. Note that this setting applies only to charts and other graphic objects. When printing in Black And White mode, cells colors are ignored.

Here's a technique that lets you see how an embedded chart looks converted to grayscale:

1. Select the chart.

2. Press Ctrl+C to copy the chart to the Clipboard.

3. Click a cell and choose Home⇒Clipboard⇒Paste⇒Picture.

4. Select the pasted picture and choose Picture Tools⇒Format⇒Adjust⇒Color and then choose the Grayscale color mode from the Recolor section of the drop-down gallery (see Figure 30-4).

FIGURE 30-4: Converting a picture of a chart to grayscale.

These steps are automated in the macro that follows. The ShowChartAsGrayScale procedure copies the active chart as a picture and converts the picture to grayscale. After you've determined whether the colors are satisfactory for grayscale printing, you can delete the picture.

Sub ShowChartAsGrayScale()

‘ Copies the active chart as a grayscale picture

‘ Embedded charts only

If ActiveChart Is Nothing Then

MsgBox “Select a chart.”

Exit Sub

End If

ActiveChart.Parent.CopyPicture

ActiveChart.Parent.TopLeftCell.Select

ActiveSheet.Pictures.Paste

ActiveSheet.Pictures(ActiveSheet.Pictures.Count). _

ShapeRange.PictureFormat.ColorType = msoPictureGrayscale

End Sub

A workbook with this example is available on the companion CD-ROM. The filename is chart to grayscale picture.xlsm.

Don't overlook the built-in grayscale chart styles. The grayscales used in these styles seem to be optimized for showing variations in chart elements.

Experimenting with Colors

Figure 30-5 shows a workbook that I created that deals with colors. If you're at all confused about how the RGB color model works, spending some time with this color demo workbook will probably make it all very clear.

This workbook, named RGB color demo.xlsm, is available on the companion CD-ROM.

FIGURE 30-5: This workbook demonstrates how red, green, and blue colors combine.

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 new 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 2010 ships with about 40 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 companion CD-ROM. 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, nontheme 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-6. 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 30-6: A color-selection control.

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 first row in each column or colors contains the “pure” color. Below each pure color are six “tint and shade” variations. Table 30-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 actually 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 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 selector 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

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 statements are included to maintain any existing 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 “Darker,” the TintAndShade property value is negative. If the color variation is expressed “Darker,” the TintAndShade property value is positive.

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 companion CD-ROM (see Figure 30-7). 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 30-7: 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 60 colors are those 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 30-8 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 companion CD-ROM.

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 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)

FIGURE 30-8: 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 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 30-9 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 30-9: 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's Auto List Members feature, which displays the possible properties and objects as you type (see Figure 30-10). This 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 30-10: 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 “Understanding document theme colors,” earlier in this chapter.

• ObjectThemeColor: A number between 1 and 10 that represents the theme color (that is, a color in the first row of the 10-x-6 theme color grid).

• 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 ever 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 SchemeColor property and (optionally) the TintAndShade property.

Shapes and theme colors

To apply theme colors to a shape, you use the ObjectThemeColor and the TintAndShade properties of the shape's Forecolor object. The following code sets the shape's color to “Accent 4, Lighter 40%.”

With ActiveSheet.Shapes(1).Fill.ForeColor

.ObjectThemeColor = msoThemeColorAccent4

.TintAndShade = 0.4

End With

In practice, that code doesn't produce the same color that's produced when you use the Fill Color control in the Ribbon.

Unfortunately, Microsoft's implementation of document themes isn't perfect. For example, I discovered that range theme colors don't match up with shape theme colors. Figure 30-11 shows range B2:D8 with a fill color of “Accent 2, Lighter 80%.” The worksheet also contains a triangle shape, which has the default fill color.

FIGURE 30-11: The goal is to write code that makes the triangle the same color as the range.

The task sounds simple enough: Make the shape's fill color the same as the range's fill color. This simple procedure should do the job:

Sub ColorShape()

With ActiveSheet.Shapes(1).Fill.ForeColor

.ObjectThemeColor = Range(“B2:D8”).Interior.ThemeColor

.TintAndShade = Range(“B2:D8”).Interior.TintAndShade

End With

End Sub

Figure 30-12 shows the result of running the ColorShape procedure. Although the shape's color is similar to the range, it's definitely not identical. Interestingly, if you apply the “Accent 2, Lighter 80%” color to the shape by using the Fill Color control on the Ribbon, the color is exactly the same as the range.

By the way, matching colors in the opposite direction doesn't work either. It's not possible to color a range by using the properties of a shape's Interior object.

Note that it is possible to transfer the exact color from the shape to the range by using the “old” color object model. The following statement makes the shape exactly the same color as the range. This color, however, doesn't change when the document theme changes.

ActiveSheet.Shapes(1).Fill.ForeColor.RGB = _

Range(“B1:D8”).Interior.Color

FIGURE 30-12: The code that should work, doesn't work. The triangle isn't the same color as the range.

I spent quite a bit of time experimenting with shape colors and range colors and reached the following conclusions:

• 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.

So it appears that colors used in shapes (and charts) are different than colors used in cells. It's more a curiosity than a problem. In actual practice, it's rarely critical that color matches between cells and objects be exact.

Shape examples

Shapes can also display other types of fills, such as gradients, pictures, and textures. Figure 30-13 shows a few examples of shapes generated by using VBA.

The workbook that contains the code that generates these shapes is available on the companion CD-ROM. The filename is shape object colors.xlsm.

FIGURE 30-13: Shapes generated with VBA.

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 30-14 shows a simple column chart named Chart 1. This chart has two data series, a legend, and a chart title.

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.

FIGURE 30-14: A simple column chart.

Refer to Chapter 18 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

• ObjectThemeColor: An integer between 0 and 16 that represents the theme color. VBA provides constants for these values. For example, msoThemeColorAccent3 contains the value 7.

• TintAndShade: A number between –1 and +1 that represents the tint or shade of the theme color.

In the previous section, I noted some problems in color matching between ranges and shapes. Those same problems apply to colors used in charts.

The examples in this section are available on the companion CD-ROM. The filename is chart colors.xlsm.

You can also specify color gradients. Here's an example that applies a preset gradient to the second data series in a chart. Notice 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