Specifying Colors

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 some 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 colors can range from 0 through 255. Therefore, the total number of possible colors is 256 — 256 — 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
Open table as spreadsheet

Name	Red Component	Green Component	Blue Component	Color Value
Black
White	255	255	255	16777215
Red	255			255
Green		255		65280
Blue			255	16711680
Yellow	255	255		65535
Pink	255		255	16711935
Turquoise		255	255	16776960
Brown	153	51		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, any of the 16,777,216 colors can be specified 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 decimal color value. You can, of course, use the RGB function to return a decimal color value. However, VBA does not have a corresponding HSL function.

Converting colors

If you know the three color component values, converting an RGB 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) '   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 not so simple. 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 function in use in a worksheet.

Figure 30-2: The DECIMAL2RGB function converts a decimal color value to its red, green, and blue components.

CD-ROM  

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 .

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 those 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 exactly to the decimal color values used by Excel.