Excel.Tips.Net Welcome toExcel.Tips.Net

Helpful Links

Tips.Net Home
ExcelTips Home
Ask an Excel Question
Make a Comment

Tips.Net Store

ExcelTips FAQ
ExcelTips Premium

Learn Access Now
Free Printable Forms

Beauty Tips
Car Tips
Cleaning Tips
College Tips
Cooking Tips
Excel2007 Tips
ExcelTips
Family Tips
Gardening Tips
Health Tips
Home Tips
Legal Tips
Money Tips
Organizing Tips
Pest Tips
Pet Tips
Wedding Tips
Word2007 Tips
WordTips

Advertise on the
ExcelTips Site

Newest Tips

Recording a Macro

Adding a Little Animation to Your Life

Converting a Range of URLs to Hyperlinks

Making the Formula Bar Persistent

Engineering Calculations

Digital Signatures for Macros

Fixing the Decimal Point

 

Official Color Names in VBA

Summary: When creating macros, you can use the ColorIndex and Color properties to set or determine the colors in a cell. Excel provides eight constants that can be used to specify colors, but that is not the limit on how you can work with colors. This tip provides some general guidance on colors and color names in VBA macros. (This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, and Excel 2003.)

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:

http://www.ozgrid.com/VBA/ReturnCellColor.htm

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 versions: 97 | 2000 | 2002 | 2003

Change Formatting Based On Your Data! Conditional formatting provides a way for you to adjust the appearance of your data based on the data itself. Discover how to put this amazingly powerful feature to work for you, today. This comprehensive volume is available in two editions.
 
Check out Excel Conditional Formatting today!