Chapter 10: Formatting Excel Objects

 < Day Day Up > 



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.

    click to expand

  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.



 < Day Day Up > 



Microsoft Excel 2003 Programming Inside Out
Microsoft Office Excel 2003 Programming Inside Out (Inside Out (Microsoft))
ISBN: 0735619859
EAN: 2147483647
Year: 2006
Pages: 161

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net