Page 251 - Microsoft Office Excel 2003 Programming Inside Out

Formatting Excel Objects

Assigning a color to a position in the color palette takes a single line of code in Excel VBA.

ActiveWorkbook.Colors (index) = RGB (red, green, blue)
But which colors should you substitute? Any you won’t use, of course, but there are actually
a number of duplicate colors in the standard palette. Why? No clue. But here are the repeats:
● Color 5 (Blue) is repeated by Color 32.
● Color 6 (Yellow) is repeated by Color 27.
● Color 7 (Magenta) is repeated by Color 26.
● Color 8 (Cyan) is repeated by Color 28.
● Color 9 is repeated by Color 30.
● Color 13 is repeated by Color 29.
● Color 14 is repeated by Color 31.
● Color 18 is repeated by Color 54.
● Color 20 is repeated by Color 34.
You should probably replace the higher-numbered color first, especially if you’re replacing a
color that is named by one of the VBA color constants. The following procedure adds a new
set of colors to the active workbook’s palette using colors 26, 27, 28, 29, and 30:
Sub CustomColors()
ActiveWorkbook.Colors(26) = RGB(240, 248, 255)
ActiveWorkbook.Colors(27) = RGB(138, 43, 226)
ActiveWorkbook.Colors(28) = RGB(165, 42, 42)
Chapter 10
ActiveWorkbook.Colors(29) = RGB(255, 250, 205)
ActiveWorkbook.Colors(30) = RGB(199, 21, 133)
End Sub
Now when you run the DisplayPalette procedure listed earlier in this chapter, you will see
your new colors in positions 26, 27, 28, 29, and 30.

Tip Getting Around the Color Limit
If you run into the 56-color barrier and don’t have room to add colors for a corporate logo,
you should insert the logo as a graphic. The colors in graphics don’t count against the
56-color limit.

Changing the color palette of a workbook is relatively straightforward, but doing it for every
workbook you create can be a pain if you try to do it by hand. However, you can write a short
macro that copies the color palette from another workbook to the active workbook. Aside
from the standard Sub and End Sub statements, you use the Workbooks collection’s Colors
property to copy the color palette from a workbook to the active workbook. If the workbook
with the desired palette were named OurColors.xls, you would use the following procedure
to copy the color palette over:

225
Part 4: Advanced VBA