Excel® 2010 Power Programming with VBA

Chapter 30: 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 2010 is no trivial matter. I'm the first to admit that it can be complicated. And often, recording a macro while you change the color of a cell or 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 any or all of those colors, but there was no way to exceed the 56-color limit for a workbook.

But things changed with the introduction of Excel 2007. You now have access to a virtually unlimited number of colors in a workbook — actually, the limit is 16,777,216 colors, but that certainly qualifies as virtually unlimited in my book.

In VBA, you can specify a color as a decimal color value, which is a number 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 ...