Excel 2013 Power Programming with VBA

Chapter 28: Working with Colors

In This Chapter

• Specifying colors in VBA code

• Using VBA conversion functions for various color models

• Converting colors to grayscale

• Working with document themes

• Modifying colors in Shape objects

• Modifying colors in charts

Specifying Colors

Dealing with color in Excel can be complicated. And often, recording a macro while you change the color of a cell or an object only adds to the confusion.

One of the most significant changes introduced in Excel 2007 was the abandonment of the old 56-color workbook palette. Back in the pre–Excel 2007 days, a workbook stored a palette of 56 colors. These colors were the only ones available for cell backgrounds, cell text, and charts. You could modify those colors, but you couldn't exceed the 56-color limit for a workbook.

The situation changed in Excel 2007, which provided access to a virtually unlimited number of colors in a workbook. (The limit is 16,777,216 colors, which I think qualifies as virtually unlimited.)

In VBA, you can specify a color as a decimal color value between 0 and 16,777,215. For example, the VBA statement that follows changes the background color of the active cell to a dark maroon:

ActiveCell.Interior.Color = 5911168

In addition, VBA has predefined constants for some common colors. For example, vbRed has a value of 255 (the decimal value for pure red), and vbGreen has a value of 65,280.

No one can keep track of nearly 17 million colors, and the predefined constants are limited. ...