Color Code rows based on a cell value in Excel 2007

Since this is an annual deal, I suggest modifying Wayne's macro (with Rory's tweak) so it runs on the active worksheet and applies all the colors at once. See the RowColoring macro in the Code Snippet pane. Even though Excel 2007 supports more Conditional Formatting criteria, you would need to install all ten on the downloaded workbook every year. With the macro, you spend a little more time in the first year to debug it, then just run the code year after year.

You'll need to get the ColorIndex numbers to fine-tune the code. Excel 2000 includes a color chart referencing the ColorIndex numbers in the VBA on-line help. Alas, this useful bit of documentation is not available in either earlier or later versions of Excel. To overcome this omission, the following sub produces a color chart in your worksheet:

Sub myColors()
Dim i As Long
For i = 0 To 14
    Cells(i + 1, 1) = i
    Cells(i + 1, 1).Interior.ColorIndex = i
    Cells(i + 1, 2) = i + 14
    Cells(i + 1, 2).Interior.ColorIndex = i + 14
    Cells(i + 1, 3) = i + 28
    Cells(i + 1, 3).Interior.ColorIndex = i + 28
    Cells(i + 1, 4) = i + 42
    Cells(i + 1, 4).Interior.ColorIndex = i + 42
Next i
End Sub

David McRitchie has a chart showing the names, ColorIndex numbers and swatches at http://www.mvps.org/dmcritchie/excel/colors.htm

If you want to use "names" for colors, Kevin (zorvek) posted code to create Enum constants at http:/Q_21472445.html#14313363


To install a sub in a regular module sheet:
1) ALT + F11 to open the VBA Editor
2) Use the Insert...Module menu item to create a blank module sheet
3) Paste the suggested code in this module sheet
4) ALT + F11 to return to the spreadsheet

To run a sub or macro:
5) ALT + F8 to open the macro window
6) Select the macro
7) Click the "Run" button

To set macro security so all macros will run (equivalent to Low security on Excel 2000 through 2003), do the following:
1) Click the Office Button (top left corner of window)
2) Click the "Excel Options" button at the bottom of the resulting dialog
3)  Go to the Trust Center tab, then click the button for "Trust Center Settings"
4) Go to the Macro Settings tab, then choose the option button for "Enable all macros (not recommended; potentially dangerous code can run)"

Excel 2007 also allows you to designate certain folders as being Trusted. Any macros contained in workbooks in those folders will be enabled automatically. A good candidate for such designation is a folder that contains a library of workbooks you have personally vetted. I don't advise so designating your main working folder, however, because you just might put a workbook containing malicious code in there at some point.


Open in New Window