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!
Copying worksheets (one or many) is easy to do manually. What is not well known is that it is even easy to make the ...Discover More
Recording macros is a great approach to getting started with macros, but at some point you'll need to create one from ...Discover More
Macros are very powerful, but you may not want them to always be available to a user. Here are some ways you can limit ...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.