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
February 2008
Applies to: Microsoft Office Excel 2007
Contents
Overview of Using Color in Excel 2007
ColorIndex Property
Color Property
Choosing Between ColorIndex and Color
Displaying ColorIndex Values
Displaying the ColorIndex Property in the Status Bar
Conclusion
Additional Resources
Overview of Using Color in 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.
expression.ColorIndex = cIndex
The Color property returns or sets the color of the object by using any one of various types of value types.
expression.Color = value
ColorIndex Property
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. ColorIndex values and colors
.gif "Cells filled with pink from the color palette")
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.
Color Property
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.
Choosing Between ColorIndex and Color
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.
ColorIndex Property
Selection.Interior.ColorIndex = 5
Color Property
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. Cells filled with shades of pink from the color palette
.jpg "ColorIndex values and colors")
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.
Table 2. Returned values for ColorIndex and Color
Cell |
ColorIndex Value Returned |
Color Value Returned |
|---|---|---|
A1 |
22 |
10040319 |
A2 |
38 |
13395711 |
A3 |
38 |
16751103 |
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.
Displaying ColorIndex Values
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.
Displaying the ColorIndex Property in the Status Bar
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.
.gif "Note") Note |
|---|
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.
Sub clearStatusBar()
Application.StatusBar = False
End Sub
Conclusion
In this article, you learned how to display the values and colors for the ColorIndex and Color properties. The ColorIndex property provides a simple way to work with cell interior and font colors. The Color property gives you more flexibility when you want to use colors in your worksheets.
Additional Resources
To learn more about the ColorIndex property and the Color property, see the following resources: