O'Reilly logo

Excel 2013 Power Programming with VBA by John Walkenbach

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required