Editor’s Note: To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO.com’s Spreadsheet Tips Page.
Reader Jean Marie B. wins a copy of Office VBA Macros You Can Use Today from CFO and MrExcel.com for submitting this question: “Thank you for these tips, but I believe they would not work if the conditional formatting would depend on more than three variables. I would like to color code rows in a range based on the value in a cell on each row. For example, I would like to color code each row in the range B2 to F10 based on data in column B for each row. The color of the rows would change as follows:
• Rest: no change
• Easy: green
• Cruising: blue
• Steady: yellow
• Brisk: orange
• Max: red (and font would be white)
I believe this could be done with a macro but I do not know how to write it.”
Jean Marie submitted this question after reading my column about conditional formatting. First, both Excel 2007 and Excel 2010 have removed the three-rule limitation for conditional formatting. If you are using a newer version of Excel, you can easily set this up using conditional formatting as shown in Figure 1 (below).
Fig. 1
If you are using Excel 2003 or earlier, and if you need to do more than three conditional formatting rules, the solution is a macro. This week’s column will discuss Jean Marie’s specific macro, but also show you how to generalize the macro for any similar situation.
Allowing Macros in Excel
By default, macros are turned off in Excel. Follow these steps once to allow macros:
• In Excel 2003 or earlier, choose Tools, Macro, Security. Change the setting to Medium.
• In Excel 2007 or later, type Alt+T followed by M then S. Choose “Disable All Macros With Notification.”
With both of these settings, you will receive a notification when you open a workbook with macros. If you are the author of the macros and know what they do, you can choose to enable the macros.
Adding the Macro to the VBA Window
Open your workbook in Excel. To switch to the VBA Editor, press Alt+F11. In the VBA Editor, choose Insert, Module, to add a blank code module to your workbook. Then copy and paste into the code pane the following lines:
Sub CustomColor()
For Each cell In Range(“B2:B10”)
Select Case cell.Value
Case “Easy”
cell.Resize(1, 5).Interior.ColorIndex = 4
cell.Resize(1, 5).Font.ColorIndex = 1
Case “Cruising”
cell.Resize(1, 5).Interior.ColorIndex = 5
cell.Resize(1, 5).Font.ColorIndex = 1
Case “Steady”
cell.Resize(1, 5).Interior.ColorIndex = 6
cell.Resize(1, 5).Font.ColorIndex = 1
Case “Brisk”
cell.Resize(1, 5).Interior.ColorIndex = 45
cell.Resize(1, 5).Font.ColorIndex = 1
Case “Max”
cell.Resize(1, 5).Interior.ColorIndex = 3
cell.Resize(1, 5).Font.ColorIndex = 2
Case “Easy”
cell.Resize(1, 5).Interior.ColorIndex = 2
cell.Resize(1, 5).Font.ColorIndex = 1
Case Else
cell.Resize(1, 5).Interior.ColorIndex = 2
cell.Resize(1, 5).Font.ColorIndex = 3
End Select
Next cell
End Sub
Use Alt+Q to close the VBA Editor and return to Excel.
Assigning the Macro to a Hot Key
One of the easiest ways to run a macro is to assign it to a shortcut key. Back in Excel, press Alt+F8 to get a list of available macros. Select the CustomColor macro and press the Options button. The hotkeys available for macros are Ctrl+a through Ctrl+z and Ctrl+Shift+A through Ctrl+Shift+Z.
Since every Ctrl+letter is already assigned to something else, it is safest to assign the macro to a shifted shortcut key, such as Ctrl+Shift+C (think of “C” for “Color”). (See Figure 2.) To specify Ctrl+Shift+C as the shortcut, type a Shift+C in the text box (for some reason, typing Ctrl+Shift+C in the box does not work, even though it feels like it should). Now, whenever you want to color the range B2:F10, you simply press Ctrl+Shift+C.
Fig. 2
Customizing the Macro for Other Ranges and Colors
Macros remain one of the most mysterious features in Excel. This section will help you to customize the macro to your own particular situation. Figure 3 (below) shows the macro again, this time with some color coding and with line numbers.
Fig. 3
Where Are the Values to Evaluate?
You need to identify where the value cells are in your worksheet. In Jean Marie’s case, the data was in cells B2:B10. If your data to evaluate is in a different range, change the address in Line 2 of Figure 3.
The red lines 2 and 26 are a special VBA macro called an object loop. The code from lines 3 through 25 will be repeated for each cell in the range specified in line 2 of Figure 3. Each time through the loop, the special variable named “cell” will refer to one cell in the range.
How Many Categories Do You Have?
The blue lines are like a huge seven-level nested IF function in Excel. For each possible value that you might encounter, you should have a set of lines such as lines 4, 5, and 6. If you have 20 categories instead of 6 categories, you could copy lines 4-6 repeatedly to handle the other conditions.
How Many Columns to Color?
Jean Marie threw a bit of a curveball into the problem when she needed to color the entire row from B through F based on the values in B. This describes a range that is one row tall and five columns wide. Any references to “cell” inside the loop would act only on column B. To color a range that is five columns wide, the .Resize(1,5) property tells VBA to act on a range that is one row tall and five columns wide, with the current cell as the upper-left corner.
What Colors to Use?
In Excel 2003, users were limited to 56 colors, and 99% of the people using Excel have never customized the color palette. So for most people, colors will be the ones shown in Figure 4 (below).
Fig. 4
The number next to each color in the index (Figure 4) is what you would specify to choose a color from this palette. Notice that there are several greens and two oranges. If Jean Marie wants the #46 orange instead of the #45 orange, change the value in line 14 of the macro. Also note that the order of the color index numbers has no correlation to the order of the colors in the Excel 2003 dropdown (Figure 5, below).
Fig. 5
If you have Excel 2007/2010, you can still use the .ColorIndex property. Or you can improve from the palette of 56 colors to all 16 million colors using .Color = RGB(255,255,255). Specify values from 0 to 255 for red, green, and blue.
Running the Macro Every Time the Worksheet Calculates
So far the macro will update every time you press Ctrl+Shift+C. This works fine for a static report; however, if you have a worksheet with formulas that are constantly recalculating (Figures 6 and 7, below), you would want the colors to update after each calculation.
Fig. 6
Fig 7
Working with nonstatic reports requires just a bit more magic. To make it work, follow these steps:
- Note the name of the current worksheet.
- Go to the VBA Editor using Alt+F11.
- Press Ctrl+R to switch to the Project Explorer.
- Find the current workbook in the Project Explorer. If necessary, click the plus sign to the left of Microsoft Excel Objects to access a list of worksheets.
- Double-click the worksheet name in the Project Explorer. This opens a secret code pane for that worksheet. At the top of the code pane are two dropdowns.
- Open the left dropdown and choose Worksheet. Instantly, Excel types a Worksheet_SelectionChange macro in the code pane. Ignore this for now.
- Open the right dropdown and choose Calculate. Excel types the beginning and ending lines or a Worksheet_Calculate macro.
- Type the macro name CustomColor inside of that macro, as shown in Figure 8 (below).
- Type Alt+Q to close VBA and return to Microsoft Excel.
Now, every time the worksheet calculates, the CustomColor macro will run, updating your range.
Fig. 8
Excel VBA Macro Resources
Macros dramatically increase what is possible with Microsoft Excel. I will be doing a series of Webcasts for CFO on May 19, August 16, and October 18 to introduce VBA. In the meantime, there is a community of passionate Excel VBA experts who regularly check in at the MrExcel Message Board. About 10,000 times each year, someone will provide a snippet of code to solve problems such as the one Jean Marie encountered. You can simply copy and paste the code into your workbook’s VBA window. It is a great way to get started with VBA. It is free to register and free to participate. Check it out by clicking here.
Bill Jelen is a CFO contributing editor, and the author of 32 books about Excel, including three editions of VBA & Macros for Microsoft Excel. You can win a copy of one of his books if your question is selected for a column. Post your question to the community content block on the right.