Adding Color to Excel 2007 Worksheets by Using the ColorIndex Property
Summary: Learn how to use the ColorIndex property to programmatically assign cell and font colors in Microsoft Office Excel 2007, and learn how to work with the values that represent color.
Rod Chisholm, Microsoft Corporation
Applies to: Microsoft Office Excel 2007
Creating custom solutions in Microsoft Office Excel 2007 sometimes requires that you use color to emphasize table values or make data easier to read. For example, you can use color to emphasize that one value is larger than another value by highlighting the background of the cell. You can use the ColorIndex property to set the colors of various objects in Excel 2007.
ColorIndex and Color are two of the properties that you can use to programmatically assign cell and font colors. The following sections describe the ColorIndex property and compare it with the Color property. You will also see examples of using the ColorIndex property.
The ColorIndex property returns or sets a variant that represents the corresponding color of an object.
The Color property returns or sets the color of the object by using any one of various types of value types.
The ColorIndex property can generate up to 57 colors. The table shown in Figure 1 illustrates the range of colors. The table was created programmatically by using a procedure similar to one shown later in this article.
Figure 1 shows the color palette and the corresponding integer value assigned to the ColorIndex property to produce the color.
The ColorIndex property can be applied to the following parent objects:
The ColorIndex property can have valid integer arguments between 0 and 56 that generate color. However, you can assign decimal or string values to the property without generating a run-time error. In these instances, Excel tries to randomly apply a color that corresponds to the argument value. However, setting the property to an integer value outside the range of 0 to 56 causes the following error:
Runtime Error '9': Subscript out of range
The range of colors that the arguments represent is not a gradual increase through the spectrum of the color palette as logic may dictate. The primary colors (red, blue, yellow, and so on) are indexed in the top 10 integer values, and additional colors represent the remainder of the 56-color palette.
Reserved numbers are generally seen when you are returning cell or font colors, but not when you are setting property values. If you select a cell that has no color added and you request Selection.Interior.ColorIndex, the result is -4142, or the xlColorIndexNone enumeration.
The Color property can be applied to the following parent objects:
The Color property is more robust than the ColorIndex property because it can handle numeric, hexadecimal, octal, or RGB values. The Color property also provides access to a wider range of color palettes.
When should you use the ColorIndex property and when should you use the Color property? The ColorIndex property is the better choice if you want to specify a color from a single color palette. Because the Color property provides access to different color palettes, it gives you more color choices. Additionally, you set or retrieve a color from the ColorIndex property by using simple integers. With the Color property, you can use hexadecimal, octal, or RGB values to specify color.
The following examples set the interior of a selection of cells to the color blue.
Selection.Interior.Color = 16711680 Selection.Interior.Color = &HFF0000 Selection.Interior.Color = &O77600000 Selection.Interior.Color = RGB(0, 0, 255)
Consider the following scenario, in which the cells are filled with shades of pink, and the different ways the two properties set or return the color fill values.
Figure 2 shows the color palette for each shade of pink that has been added to cells A1, A2, and A3.
The following code displays the ColorIndex and the Color values in the Immediate window in the Visual Basic Editor. The example assumes that the colored cells are A1, A2, and A3.
Sub showCIndexColor() For row = 1 To 3 Debug.Print "ColorIndex = " & Cells(row, 1).Interior.ColorIndex Debug.Print "Color = " & Cells(row, 1).Interior.Color Next row End Sub
The resulting values will resemble those shown in Table 2.
ColorIndex Value Returned
Color Value Returned
One way to see this in action is to open the Visual Basic Editor, and with one of the cells selected, type and run ?Selection.Interior.ColorIndex in the Immediate window. The ColorIndex property returns the same value for the colors in cells A2 and A3, whereas the Color property proves they are not equal. Conversely, if you select a cell that has no fill color and then type and run Selection.Interior.ColorIndex = 22 in the Immediate window, the cell actually fills with the color orange instead of pink.
ColorIndex only allows colors to be set for values between 0 and 56. However, when you set the property equal to a value other than an integer, instead of returning an error, it makes the closest match. Thus, the color that is displayed might be inaccurate.
The following example uses the ColorIndex property to fill each cell in the range with the background color and text that describes the interior and font color. The For…Next loop represents the number of passes needed to display all 57 colors. The Cells(row, column),row value is increased with cIndex+1 to avoid an error that would occur by starting at row 0.
For cIndex = 0 To 56 Cells(cIndex + 1, 1).Interior.ColorIndex = cIndex Cells(cIndex + 1, 1).Value = "Interior.ColorIndex = " & cIndex Cells(cIndex + 1, 2).Font.ColorIndex = cIndex Cells(cIndex + 1, 2).Value = "Font.ColorIndex = " & cIndex If Cells(cIndex + 1, 1).Interior.ColorIndex = 1 _ Then Cells(cIndex + 1, 1).Font.ColorIndex = 48 Next cIndex Columns(1).EntireColumn.AutoFit Columns(2).EntireColumn.AutoFit
The If…Then statement sets the automatic font color to gray to make the cells that have a black background easier to see. The last two lines in the example resize the columns so that the data fits in the columns correctly.
Another way to work with the ColorIndex property is to use a button or some code that is triggered on a worksheet event that returns the color of the active cell. In the following example, the color index value of the fill and font color of the selected cell is displayed in the Status bar. The code is added to the Worksheet_SelectionChange event procedure. When a new cell is selected, the code runs and displays the information in the Status bar.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.StatusBar = "Current Cell: Interior.ColorIndex = " & _ ActiveCell.Interior.ColorIndex & " / Font.ColorIndex = " & _ ActiveCell.Font.ColorIndex End Sub
If you are using the Status bar to monitor information, you might have to clear and return the status to Ready. Otherwise, the Status bar retains the color index setting until the application is closed.
A full calculation of the workbook or another workbook that is opened in the same instance of Excel does not reset the Status bar.
To reset the Status bar, run the following procedure.