Back
Excel

Modifying Conditional Formatting Color Ranges in Excel 2007

Today’s authors, Amit Velingkar, a Program Manager on the Excel team, and Bob Silverstein, a User Experience Researcher on the Excel team, talk about creating professional looking Color Ranges in Excel.

Introduction:

My fellow PM on Excel, Robin Wakefield, wrote a blog article on how to create professional looking charts in Excel 2007. Continuing with the same theme, we will investigate the idea of tweaking Color Ranges in Excel 2007. We will start with a default Conditional Formatting Color Range and modify it to give it more pastel look. Also, this would be an ideal forum to give us your feedback on existing conditional formatting colors used in Excel 2007.

As a part of my preparation for this article, I did two things – researched customer files that use conditional formatting and explored various options with Microsoft designers.

Looking at some customer files, I quickly observed a trend. I found out that many users wanted to highlight a small portion of their data. They were achieving this by changing some of the colors in their color ranges to white. Also, some customers preferred a pastel shade of the same color.

I discussed this trend with Microsoft designers and they taught me some neat tricks about manipulating colors. First, they told me that choosing the correct shade of white was critical while mixing colors. And last, an intuitive way of changing shades of colors was to use the HSL (Hue, Saturation and Luminosity) model.

My blog article attempts to bring all these lessons together. We will change some of the colors to white and tweak the colors shades to pastel. Finally, we will introduce a VBA macro that lets you quickly try out new color shades.

Procedure:

1. I started out with applying three of our default color combinations to some data. I choose the following color ranges: Red-Yellow-Green, Red-Yellow-Blue and Yellow-Green.

clip_image002

2. I replaced the yellow color with the appropriate shades of “whiteâ€.

Changing the yellow colors to pure whites created a high contrast. For creating whites with a good spectrum of colors, we crank up the RGB values all the way to 255 creating a pure white – Remember RGB (255,255,255) is white. Then, remove just a little amount of the participating color to create “target†white.

As an example while mixing Red and Green, we set our white to (250,250,255) by removing some red and green. The end result is a color range containing pure shades of Red and Green.

Color 1

Color 2

White

Notes

Red (255,0,0)

Green (0,255,0)

(250,250,255)

Remove some red and green

Red (255,0,0)

Blue (0,0,255)

(250,255,250)

Remove some red and blue

Red(255,0,0)

-

(250,255,255)

Remove some red

Green (0,255,0)

-

(255,250,255)

Remove some green

Select the color range. On the Home tab, click on Conditional Formatting, then Manage Rules. In the Conditional Formatting Rules Manager, select the Conditional Format and click on Edit Rule. In the Edit Formatting Rule dialog change the yellow color to white.

clip_image002[5]

3. Using HSL Model to change non-white colors

We can use the HSL model to manipulate color. HSL defines the color spectrum based on Hue (base color from the rainbow spectrum), Luminosity (brightness) and Saturation (purity of the color).

Select the color range. On the Home tab, click on Conditional Formatting, then Manage Rules. In the Conditional Formatting Rules Manager, select the Conditional Format and click on Edit Rule. In the Edit Formatting Rule dialog, click on the color dropdown and choose More Colors. Click on the Custom tab and choose HSL in the Color model dropdown.

In this example, we will change colors using the HSL models as follows:

o Hue: Unchanged, this leaves the base color unchanged. Hence our Reds stay Red.

o Saturation: Decrease the Saturation to 150. This makes the colors more pastel.

o Luminosity: Increase the luminosity to 200. The goal is to increase the brightness to make the color more visible and showcase the text behind the color ranges.

clip_image002[7]

Result:

By using shades of white and by tweaking the luminosity and saturation of colors, we changed the color ranges as follows:

image

HSL Color Manipulation using VBA:

Since I wanted to try out a lot of combinations of Luminosity and Saturation values to tweak color ranges, I decided to write a VBA macro to do this. I quickly learnt that while Excel allows HSL manipulation through the UI, it does not support HSL manipulation through VBA. I looked around for ways to manipulate HSL and finally found a blog post by Tony Jollans that did something similar. Although most of the code used is directly from Tony Jollans’s blog post, I wrote some additional subroutines, added some comments and re-factored some of the subroutines for my purpose.

The entry function for my macro is called ChangeColorRangeColors().

Sub ChangeColorRangeColors()
   …

   For Each clrScale In Selection.FormatConditions
      For Each criteria In clrScale.ColorScaleCriteria
         ‘ get clr
         clr = criteria.FormatColor.Color

         ‘ split RGB
         SplitRGB clr, R, G, B

        ‘skip whites – when all RGB componenst are above 240
         If R < 240 Or G < 240 Or B < 240 Then

            ‘ get HSL
            RGBtoHSL clr, H, S, L

            ‘ increase luminosity
            L = (iLuminosity / 255) ‘ from 0.0 to 1.0

            ‘ decrease saturation
            S = (iSaturation / 255) ‘ from 0.0 to 1.0

            ‘ get RGB
            HSLtoRGB H, S, L, clr

            ‘ set new clr
            criteria.FormatColor.Color = clr

         End If
      Next
   Next
End Sub

At a high level, my HSL manipulator code works in the following ways:

  1. Get the RGB encoded value from the selected color ranges.
  2. Split the RGB encoded values into the Red, Green and Blue components.
  3. Skip the white colors – we do not want to change these.
  4. Covert RGB into HSL.
  5. Modify the Saturation and Luminosity, leaving Hue unchanged.
  6. Convert the modified HSL values back to encoded RGB.
  7. Apply the new RGB value back to the color range.

I won’t go into the details of converting between RGB and HSL values. These conversion routines are well documented and can be freely found on the internet.

However, another tricky part was trying to split the encoded color value that is used by Excel into individual RGB components. VBA provides a function, aptly named RGB(), that returns an encoded long value when provided with the R,G and B components. I have used this function in my macro to set the Color Range colors. However, VBA does not provide a reverse function to split this encoded long value. I have used a custom function called SplitRGB to do this.

Sub SplitRGB(RGB As Long, R As Double, G As Double, B As Double)
  
Dim HexString As String

   ‘ convert the long to Hex – bb:gg:rr
  
HexString = Hex(RGB)

   ‘ in order to get r,g,b components out of the string,
   ‘ we have to make it is atleast 6 characters long – bb:gg:rr
  
HexString = Right(String$(5, “0″) & HexString, 6)

   ‘ get each individual color and convert to an double (range: 0 to 255)
  
R = CDbl(“&H” & Mid$(HexString, 5, 2))
  
G = CDbl(“&H” & Mid$(HexString, 3, 2))
  
B = CDbl(“&H” & Mid$(HexString, 1, 2))
End Sub

Since the R, G, B values span from 0 to 255 and can be accommodated in 1 byte. Excel uses a 4-Byte long to encode the RGB values. The first byte is used to store the red value, the second byte stores the green value and the third byte stores the blue values. An easy way to observe these values is to convert this encoded long into a hexadecimal string. We then split this string using the Mid function to isolate the R, G, B components. Finally, we use a conveniently provided CDbl function to convert hexadecimal text to a number. Observe that appending the “&H†enables the CDbl function to identify the text as hexadecimal.

To recap, We can create professional looking Conditional Formatting color ranges by choosing the correct shades of whites and using the HSL model to tweak Saturation and Luminosity values. I am hoping that by using these techniques and subroutines, you will be able to automate the look and feel of your Excel Spreadsheets.

The attached workbook contains these color ranges and the VBA macros. We would love to hear your feedback on these colors.