Chapter 10: Formatting Excel Objects

---

If left in their original condition, Microsoft Excel worksheets are fairly plain to look at. Black text on a white background, with gray gridlines indicating cell barriers, is a functional way to present data, but it's not the most exciting presentation imaginable. Not to mention the fact that if all of your data looks the same, there's nothing to distinguish one cell from another except for the value. And, yes, the values are the most important part of a worksheet, but presentation does count for something.

In this chapter you'll learn how to use Excel color constants and the Excel color palette, modify the existing color palette, find out how to get around the 56-color limit in Excel, and format cells by changing their interior, font, and border colors.

Using Excel Color Constants and the Color Palette

Although there are a lot of ways you can reformat the elements of a worksheet, one great way to call attention to an element is by changing that element's color. Whether you change the color of text in a cell to reflect the cell's value, change the color of a sheet tab to indicate there was a change made to the worksheet's data, or make a cell's background yellow to emphasize that it is the active cell, you can use colors to make your data stand out and your sheets easier to use.

The simplest way to assign a color to a worksheet element is to use one of the Microsoft Visual Basic for Applications color constants, which represent the eight basic colors available on a computer. The constants and the colors they represent are listed in Table 10-1.

Table 10-1: The RGB Values of the VBA Color Constants

Constant	Description	Constant	Description
vbBlack	Black	vbRed	Red
vbGreen	Green	vbYellow	Yellow
vbBlue	Blue	vbMagenta	Magenta
vbCyan	Cyan	vbWhite	White

Why are these colors the only ones with constants assigned to them? The reason lies in how colors are created on a computer. You probably encountered the color wheel sometime, probably in school, and you learned that you can combine red, blue, and yellow pigments to create any color you want. That's true for pigments (usually paint or ink), but it's not true when you're working with light. When you want to create colors using light, you operate with these three primary colors: red, green, and blue.

The difference between working with pigments and working with light is captured in the names of the two color systems: subtractive color, which refers to pigments, and additive color, which refers to light. In the subtractive color system, you begin with white (the absence of color) and, through the use of pigments, 'subtract' colors by blocking them out with your paint. If you mix equal amounts of the primary colors (red, yellow, and blue) you'll get black because you've subtracted all of the colors. In the additive color system, by contrast, you start with black (the absence of light) and add colors to the mix. In the additive color system, adding full-intensity red, green, and blue light, you get white light.

Just as you can mix differing amounts of paint to produce unique colors (for example, mixing equal amounts of red and yellow makes true orange, whereas putting in more red than yellow makes a red-orange), you can mix differing amounts of light to create distinct colors on your computer. The Microsoft Windows operating system recognizes 256 intensities for each primary color (red, green, and blue). An intensity of 0 means that none of that light is added to the color of a pixel (a dot on your monitor screen), and an intensity of 255 means that the maximum amount of that color is added.

Note 

A pixel is actually made up of three dots: one that emits red light, one that emits green light, and one that emits blue light.

To define a color using a mixture of red, green, and blue light, you use the Visual Basic RGB function, which has this syntax:

RGB(red, green, blue) 

In this function, red is the amount of red light to be used, green is the amount of green light to be used, and blue is the amount of blue light to be used. (Any value over 255 is assumed to be 255.) Table 10-2 lists the RGB values for the eight colors assigned to the VBA color constants.

Table 10-2: The RGB Values of the VBA Color Constants

Constant	R	G	B
vbBlack	0	0	0
vbRed	255	0	0
vbGreen	0	255	0
vbYellow	255	255	0
vbBlue	0	0	255
vbMagenta	255	0	255
vbCyan	0	255	255
vbWhite	255	255	255

Note 

There are 16,777,216 possible RGB color combinations, but the eight colors in Table 10-2 have variable names assigned to them because they are the simplest colors, representing the eight combinations available when the individual pixel lights are either on at full intensity or off.

You can use the RGB function to apply a color directly to an element of your worksheet, but there is an important limitation you need to know about in Excel: the program can only display 56 colors at a time, and Excel keeps track of those 56 colors in the Excel color palette. If the color you attempt to assign to the element isn't in the color palette, Excel displays the closest color that is in the palette.

Table 10-3 lists the RGB values of the colors assigned to each of the entries in the standard Excel color palette, along with the name of that color.

Table 10-3: The RGB Values of the Standard Excel Colors

Name	R	G	B
Black (Color 1)	0	0	0
White (Color 2)	255	255	255
Red (Color 3)	255	0	0
Green (Color 4)	0	255	0
Blue (Color 5)	0	0	255
Yellow (Color 6)	255	255	0
Magenta (Color 7)	255	0	255
Cyan (Color 8)	0	255	255
Color 9	128	0	0
Color 10	0	128	0
Color 11	0	0	128
Color 12	128	128	0
Color 13	128	0	128
Color 14	0	128	128
Color 15	192	192	192
Color 16	128	128	128
Color 17	153	153	255
Color 18	153	51	102
Color 19	255	255	204
Color 20	204	255	255
Color 21	102	0	102
Color 22	255	128	128
Color 23	0	102	204
Color 24	204	204	255
Color 25	0	0	128
Color 26	255	0	255
Color 27	255	255	0
Color 28	0	255	255
Color 29	128	0	128
Color 30	128	0	0
Color 31	0	128	128
Color 32	0	0	255
Color 33	0	204	255
Color 34	204	255	255
Color 35	204	255	204
Color 36	255	255	153
Color 37	153	204	255
Color 38	255	153	204
Color 39	204	153	255
Color 40	255	204	153
Color 41	51	102	255
Color 42	51	204	204
Color 43	153	204	0
Color 44	255	204	0
Color 45	255	153	0
Color 46	255	102	0
Color 47	102	102	153
Color 48	150	150	150
Color 49	0	51	102
Color 50	51	153	102
Color 51	0	51	0
Color 52	51	51	0
Color 53	153	51	0
Color 54	153	51	102
Color 55	51	51	153
Color 56	51	51	51

Yes, once you get beyond the eight colors that are assigned to Visual Basic constants, the names of the colors reflect the color's position in the palette's index. That decision makes sense, though, once you realize that you as a programmer (or as a user of the main Excel program) can assign new RGB values to any of the slots in the palette.

Note 

The color palette, as referred to here, isn't the same as the palette displayed in the main Excel program. It's strictly used as an internal representation of the colors.

You can assign a color to a worksheet element by referring to a position in the Excel color palette. To do so, you use the Workbook object's Colors property, which has this syntax:

Workbook.Colors (index)

In the preceding code, index is any number from 1 to 56. If you have a pie chart with divisions for all 100 products sold by your company, you'll get some repeat colors. That's not such a bad thing…the colors should be far enough apart on the chart so that your colleagues won't have any trouble distinguishing the divisions. As an alternative, you can always create a chart in which you display the proportion of sales attributed to the top 50 products (with a single wedge for the other 50 products), and then break out the sales of the remaining products on a separate chart.

Manipulating the Current Excel Color Palette

Realizing that you're limited to using the colors in the color palette for a given workbook, you'd probably be interested in a procedure that lets you display the colors in the current palette. The DisplayPalette procedure does just that.

Sub DisplayPalette()
       Range("A1").Select
       ActiveCell.Formula = "Color"
       ActiveCell.Offset(0, 1).Formula = "Index"
       ActiveCell.Offset(1, 0).Activate
       For NumColor = 1 To 56
          With ActiveCell.Interior
             .ColorIndex = NumColor
             .Pattern = xlSolid
             .PatternColorIndex = xlAutomatic
          End With
          ActiveCell.Offset(0, 1).Formula = NumColor
          ActiveCell.Offset(1, 0).Activate
       Next NumColor
End Sub  

Caution 

A version of this procedure was originally published on the Microsoft Knowledge Base at http://support.microsoft.com/support/kb/articles/q149/1/70.asp, but that version of the program has a variable naming error that causes it to repeat the same color in all 56 cells. For debugging practice, run the code, find out what happens, and see if you can spot the error (provided the MSDN folks haven't corrected it).

But what if the colors in the palette aren't what you need for your designs? Many organizations have policies regarding the specific colors used in and appearance of their logos and official documents. Rather than be forced to work with colors that might not be exactly right, you can substitute a custom color for an existing color in the palette.

To assign a new color to a slot in the Excel color palette from within the Excel program, follow these steps:

1. Click Tools, Options, and then click the Color tab in the Options dialog box.

   

2. Click the square of the color you want to replace, and then click Modify. If you see the color you want in the Colors dialog box that appears, click it and then click OK. If you don't see the color you want, click the Custom tab.

3. Verify that RGB is selected in the Color Model list box.

4. Type the red, blue, and green components of your color in the appropriate boxes.

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)
   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:

Sub GetOurColors()
    ActiveWorkbook.Colors = Workbooks("OurColors.xls").Colors
End Sub

To copy a color palette from another workbook using the Excel interface, follow these steps:

1. Open the workbook from which you want to copy the color palette.

2. In the workbook to which you want to copy the color palette, click Tools, Options.

3. Click the Color tab in the Options dialog box.

4. Click the Copy Colors From down arrow, and then click the name of the workbook from which you want to copy the color palette.

If you've changed the default Excel color palette, you can change it back to the default by calling the Workbook object's ResetColors method, as in the following procedure:

Sub NormalColors()
ActiveWorkbook.ResetColors
End Sub

To change the color palette back to the default using the Excel interface, follow these steps:

1. Click Tools, Options.

2. Click the Color tab in the Options dialog box.

3. Click Reset.

Using the Excel Color Palette on the Web

If you've used the Web a lot, you've probably found that some pages that appear perfectly normal in one browser are a mess in another browser. Whether the spacing is off, the text is larger in some paragraphs than in others, or the colors are different, you just don't get what you expect. Part of the difficulty can be traced to older Web browsers that don't understand the newer markup tags, but another reason is that some programs, such as Microsoft FrontPage, tend to create code that is interpreted perfectly by Microsoft Internet Explorer but less well by other browsers. Colors are no different from other HTML formatting instructions, so you need to know what can cause the errors and how to avoid them.

Beginning with Internet Explorer 4.0, there has been a list of named colors with associated RGB color values that the browser knows how to interpret. Not all browsers know how to interpret the names of the colors, but every major browser is able to interpret RGB color values. Table 10-4 lists the standard HTML colors and their associated RGB values so that you can add them to your Excel color palette if you want to create a workbook that looks good both in print and on the Web.

Table 10-4: The RGB Values of Standard HTML Colors

Color	R	G	B	Color	R	G	B
aliceblue	240	248	255	antiquewhite	250	235	215
aqua	0	255	255	aquamarine	127	255	212
azure	240	255	255	beige	245	245	220
bisque	255	228	196	black	0	0	0
blanchedalmond	255	235	205	blue	0	0	255
blueviolet	138	43	226	brown	165	42	42
burlywood	222	184	135	cadetblue	95	158	160
chartreuse	127	255	0	chocolate	210	105	30
coral	255	127	80	cornflowerblue	100	149	237
cornsilk	255	248	220	crimson	220	20	60
cyan	0	255	255	darkblue	0	0	139
darkcyan	0	139	139	darkgoldenrod	184	134	11
darkgray	169	169	169	darkgreen	0	100	0
darkkhaki	189	183	107	darkmagenta	139	0	139
darkolivegreen	85	107	47	darkorange	255	140	0
darkorchid	153	50	204	darkred	139	0	0
darksalmon	233	150	122	darkseagreen	143	188	139
darkslateblue	72	61	139	darkslategray	47	79	79
darkturquoise	0	206	209	darkviolet	148	0	211
deeppink	255	20	147	deepskyblue	0	191	255
dimgray	105	105	105	dodgerblue	30	144	255
firebrick	178	34	34	floralwhite	255	250	240
forestgreen	34	139	34	fuchsia	255	0	255
gainsboro	220	220	220	ghostwhite	248	248	255
gold	255	215	0	goldenrod	218	165	32
gray	128	128	128	green	0	128	0
greenyellow	173	255	47	honeydew	240	255	240
hotpink	255	105	180	indianred	205	92	92
indigo	75	0	130	ivory	255	255	240
khaki	240	230	140	lavender	230	230	250
lavenderblush	255	240	245	lawngreen	124	252	0
lemonchiffon	255	250	205	lightblue	173	216	230
lightcoral	240	128	128	lightcyan	224	255	255
lightgoldenrodyellow	250	250	210	lightgreen	144	238	144
lightgrey	211	211	211	lightpink	255	182	193
lightsalmon	255	160	122	lightseagreen	32	178	170
lightskyblue	135	206	250	lightslategray	119	136	153
lightsteelblue	176	196	222	lightyellow	255	255	224
lime	0	255	0	limegreen	50	205	50
linen	250	240	230	magenta	255	0	255
maroon	128	0	0	mediumaquamarine	102	205	170
mediumblue	0	0	205	mediumorchid	186	85	211
mediumpurple	147	112	219	mediumseagreen	60	179	113
mediumslateblue	123	104	238	mediumspringgreen	0	250	154
mediumturquoise	72	209	204	mediumvioletred	199	21	133
midnightblue	25	25	112	mintcream	245	255	250
mistyrose	255	228	225	moccasin	255	228	181
navajowhite	255	222	173	navy	0	0	128
oldlace	253	245	230	olive	128	128	0
olivedrab	107	142	35	orange	255	165	0
orangered	255	69	0	orchid	218	112	214
palegoldenrod	238	232	170	palegreen	152	251	152
paleturquoise	175	238	238	palevioletred	219	112	147
papayawhip	255	239	213	peachpuff	255	218	185
peru	205	133	63	pink	255	192	203
plum	221	160	221	powderblue	176	224	230
purple	128	0	128	red	255	0	0
rosybrown	188	143	143	royalblue	65	105	225
saddlebrown	139	69	19	salmon	250	128	114
sandybrown	244	164	96	seagreen	46	139	87
seashell	255	245	238	sienna	160	82	45
silver	192	192	192	skyblue	135	206	235
slateblue	106	90	205	slategray	112	128	144
snow	255	250	250	springgreen	0	255	127
steelblue	70	130	180	tan	210	180	140
teal	0	128	128	thistle	216	191	216
tomato	255	99	71	turquoise	64	224	208
violet	238	130	238	wheat	245	222	179
white	255	255	255	whitesmoke	245	245	245
yellow	255	255	0	yellowgreen	154	205	50

For more information on how these colors will look on your monitor, see the full-color table on MSDN, the Microsoft Developer Network site, at: http://msdn.microsoft.com/workshop/author/dhtml/reference/colors/colors.asp.

For more information on how to publish Excel worksheets and workbooks to the Web, see Chapter 25, 'Excel and the Web.'

Some of the work of matching colors from the Excel palette to the Web has been done for you. Table 10-5 lists the colors in the Excel palette that closely correspond to the standard HTML colors.

Table 10-5: Excel Palette Colors That Correspond to HTML Colors

Excel Color	HTML Color	R	G	B
Color 1 (Black)	black	0	0	0
Color 2 (White)	white	255	255	255
Color 3 (Red)	red	255	0	0
Color 4 (Green)	lime	0	255	0
Color 5 (Blue)	blue	0	0	255
Color 6, 27 (Yellow)	yellow	255	255	0
Color 7, 26 (Magenta)	fuschia	255	0	255
Color 8, 28 (Cyan)	aqua	0	255	255
Color 9, 30	maroon	128	0	0
Color 10	green	0	128	0
Color 11, 25	navy	0	0	128
Color 12	olive	128	128	0
Color 13, 29	purple	128	0	128
Color 14, 31	teal	0	128	128
Color 15	silver	192	192	192
Color 16	gray	128	128	128
Color 19	lightyellow	255	255	204
Color 44	gold	255	204	0
Color 45	darkorange	255	153	0

Caution 

Some of the Excel palette and HTML colors in this list actually differ by a small amount, but the colors are practically identical to the human eye.

---