• Technology
  • CFO.com | US

Spreadsheets: Color My World, with Macros

Make quick work out of color-coding data that needs to be continually updated. Learn how by using this step-by-step guide.


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

MrExcel 3-9-11 Fig01

 

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

Discuss

Your email address will not be published. Required fields are marked *