Excel® 2007 Power Programming with VBA

Chapter 30. Working with Colors

In This Chapter

One of the most significant changes in Excel 2007 is the abandonment of the old 56-color workbook palette. This chapter describes how color is used in Excel 2007, including the new themes. I include many examples that should help you understand how these changes affect your workbooks. Here you will find

• Specifying colors in VBA code

• VBA conversion functions for various color models

• Converting colors to grayscale

• Working with Excel 2007 document themes

• Modifying colors in Shape objects

• Modifying colors in charts

Dealing with color in Excel 2007 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. In this chapter, I attempt to pull it all together.

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. Sure, you could modify any or all those colors, but there was no way to exceed the 56-color limit for a workbook.

But things have changed. 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.

Specifying Colors

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