Chapter 30: Working with Colors


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 ...

Get Excel® 2010 Power Programming with VBA now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.