Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

When you create worksheets and charts , it's important to remember that not everyone has a color printer. And even if your chart is printed on a color printer, it's possible that it may be photocopied, faxed, or viewed by someone who is color-blind (a condition that affects about 8 percent of the male population).

When content is printed on a non-color device, colors are converted to grayscale. Sometimes you'll be lucky and your colors will display nicely when converted to grayscale. Other times, you won't be so lucky. For example, the columns in a chart may be indistinguishable when the colors are converted.

Every grayscale color has an equal component of red, green, and blue. Pure black is RGB(0, 0, 0) . Pure white is RGB(255, 255, 255) . Neutral gray is RGB(128, 128, 128) . Using this color system produces 256 shades of gray.

To create a 256-color grayscale in a range of cells, execute the procedure that follows . It colors the background of cells in the range A1:A256, starting with black and ending with white. You might want to zoom out on the worksheet to see the entire range.

Sub GenerateGrayScale() Dim r As Long For r = 0 To 255 Cells(r + 1, 1).Interior.Color = RGB(r, r, r) Next r End Sub

Converting colors to gray

One approach to grayscale conversion is to simply average the Red, Green, and Blue components of a color and use that single value for the Red, Green, and Blue components of its grayscale equivalent. That approach, however, does not take into account the fact that different colors are perceived as varying levels of brightness. For example, green is perceived to be brighter than red, and red is perceived to be brighter than blue.

Perceptual experiments have arrived at the following "recipe" to convert an RGB color value to a grayscale value

• 28.7% of the red component

• 58.9% of the green component

• 11.4% of the blue component

For example, consider color value 16751001, a shade of violet that corresponds to RGB(153, 153, 255) . Applying the factors listed previously, the RGB values are

• Red: 28.7% — 153 = 44

• Green: 58.9% — 153 = 90

• Blue: 11.4% — 255 = 29

The sum of these values is 163. Therefore, the corresponding grayscale RGB value for color value 16751001 is RGB(163, 163, 163) .

Following is a VBA function that accepts a decimal color value as its argument and returns the corresponding grayscale decimal value.

Function Grayscale(color) Dim r As Long, g As Long, b As Long r = (color \ 256 ^ 0 And 255) * 0.287 g = (color \ 256 ^ 1 And 255) * 0.589 b = (color \ 256 ^ 2 And 255) * 0.114 Grayscale = RGB(r + g + b, r + g + b, r + g + b) End Function

Viewing charts as grayscale

One way to approximate how your colors will look when converted to grayscale is to use Excel's Print Preview feature with the printer set to a non-color device (such as a fax).

Here's a technique that lets you see how an embedded chart looks converted to grayscale:

1. Select the chart.

2. Press Ctrl+C to copy the chart to the Clipboard.

3. Click a cell and choose Home Clipboard Paste Paste As Picture.

4. Select the picture and choose Picture Tools Format Recolor and then choose the Grayscale color mode from the drop-down gallery.

These steps are automated in the macro that follows. The ShowChartAsGrayScale procedure copies the active chart as a picture and converts the picture to grayscale. After you've determined whether the colors are satisfactory, you can delete the picture.

Sub ShowChartAsGrayScale() ' Copies the active chart as a grayscale picture ' Embedded charts only If ActiveChart Is Nothing Then MsgBox "Select a chart." Exit Sub End If ActiveChart.Parent.CopyPicture ActiveChart.Parent.TopLeftCell.Select ActiveSheet.Pictures.Paste ActiveSheet.Pictures(ActiveSheet.Pictures.Count). _ ShapeRange.PictureFormat.ColorType = msoPictureGrayscale End Sub