by Allen Wyatt
(last updated November 8, 2014)
Excel uses a color palette consisting of 56 colors. You can see these colors if you display the Patterns tab of the Format Cells dialog box. When creating macros in VBA, you may want to refer to these colors, by name, using constants.
Unfortunately, Excel's VBA doesn't have constants defined for each of the 56 colors in the palette. The only colors defined, by name, are members of the ColorConstants class, and there are eight members of the class: vbBlack, vbWhite, vbRed, vbGreen, vbBlue, vbYellow, vbMagenta, and vbCyan.
In VBA you can use the ColorIndex property to define which color you want to use from Excel's palette. The problem is that ColorIndex is not a color; it is an index into the palette. Thus, a ColorIndex of 1 is the first color in the palette, 2 is the second, and so on. You can see this in action by looking at the sample code at this URL:
This code examines the ColorIndex property for a cell and returns a color name. The name returned, however, is not a constant for the color; it is only a description of what color the palette at that index appears to be.
If you want to set the color of a cell, you actually should use the Color property. This property allows you to use the eight VBA color constants mentioned earlier. It just so happens that if you use these Color property to set the interior color of a cell, you'll find that the eight named colors correspond to ColorIndex values of 1 through 8. The following macro illustrates this nicely:
Sub CheckColors() Dim arr8Colors As Variant Dim i As Integer arr8Colors = Array( _ vbBlack, vbWhite, vbRed, vbGreen, _ vbBlue, vbYellow, vbMagenta, vbCyan) For i = 0 To 7 Selection.Offset(i, 0).Interior.Color = arr8Colors(i) Selection.Offset(i, 1).Value = Selection.Offset(i, 0).Interior.ColorIndex Next i End Sub
This correspondence for the first eight values between Color and ColorIndex should only be taken as an artifact of history, dating back to the days when Excel only allowed you to use eight colors—the eight colors defined with VBA constants. If you want to specify some other color for a cell, you should use the RGB function to specify the Color property, as shown here:
Selection.Interior.Color = RGB(128, 64, 255)
The RGB function allows you to specify the red, green, and blue components of any color. Each component can range in value from 0 to 255.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2778) applies to Microsoft Excel 97, 2000, 2002, and 2003.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
VBA libraries are used to provide some functions to your macros. These libraries are often tied to the version of Excel ...Discover More
Macros that run automatically when you open or close a workbook are quite helpful. You may not want them to run, however, ...Discover More
Many times you need to select just the visible cells before taking some action. It is helpful to know how to make this ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.