The VBA SpecialCells method combined with background colors can be a simple and effective method to perform high-level worksheet audits. The process works by using a macro to apply colors to individual cells flagged as per the SpecialCells criteria.

For example, in Excel 2003 when you go to Edit -> Go To... -> Special, the following dialog box appears:

Options are available that allow you to select cells with constants, formulas, blanks, etc.

The same can be done with VBA and the SpecialCells method. The advantage of using VBA is that you can apply background colors to make the cells quickly visible. Additionally, there are even more options available via VBA than what you see in the dialog box.

To explain, you're boss asks you to audit the accuracy of a simple financial statement. To do so, start by making sure that formulas appear where they logically should. A simple VBA procedure using the SpecialCells method as presented below can be used to color all of the cells with formulas in the desired range yellow (yellow is tied to the index value number "6" below - this may be different on your machine.)

Sub Flag_Formulas()
    Dim Rng As Range
    Set Rng = Range("F9:I32")
    Rng.SpecialCells(xlCellTypeFormulas).Interior.ColorIndex = 6
End Sub

After running the macro, the financial statement looks like this:

A quick look at the statement reveals that something is wrong in cell H19. Instead of seeing a cell with a formula it looks like somebody hard-coded a number.

This same process can be applied to other types of cells. The macro below can be used to flag hard-codes numbers:

Sub Flag_Hard_Coded_Numbers()
    Dim Rng As Range
    Set Rng = Range("F9:I32")
    Rng.SpecialCells(xlCellTypeConstants, 1).Interior.ColorIndex = 6
End Sub

This macro flags text entries:

Sub Flag_Text()
    Dim Rng As Range
    Set Rng = Range("F9:I32")
    Rng.SpecialCells(xlCellTypeConstants, 2).Interior.ColorIndex = 6
End Sub

For colors, if you want to specify an RGB number rather than an index number you can use a statement like:

Rng.SpecialCells(xlCellTypeConstants, 2).Interior.Color = RGB(255, 255, 204)

Finally, if you look up "SpecialCells" in Excel's VBA help there are even more options available. You can flag cells with comments, validation, same formatting, and even more.

Typo: the first VBA procedure highlights blanks, not formulas. Also note that until Excel 2007, using RGB doesn’t always use the RGB value you specified, it uses the color in the palette that Excel decides is closest to the stated RGB (and sometimes it’s not very close).

Jon,

Thanks for catching that.  That’s also interesting about the RGB - I didn’t know that.

Do you think you’ll do a User Conference in the west sometime?

John

John -

There was one conference in California a couple years ago, which I did not attend. Last year we did one in St Louis, which I presented and taught at. The Atlantic City ones seem to do best: we have folks come down from NYC, and some come up from DC, some from in-between, and some even fly in from wherever (including last week from Germany and the UK).

I’m doing an Excel Visualization and Dashboards Boot Camp with Mike Alexander in a few weeks in Texas. If this does well, and I think it will, we are likely to offer it in various venues in the future. See this blog entry for information:
http://peltiertech.com/WordPress/2008/08/04/excel-dashboard-and-visualization-boot-camp/

I think this is a great extension to the standard Excel functionality. Do you have an idea of easily save the original color to revert the flag macro afterwards?

Joop,

My first thought was to use Copy at the start of the routine and then Paste Special Formats at the end of the routine to reverse the effects of the macro.  However, I’m having trouble keeping the formatting saved via the clipboard.  If and/or when I can figure it out I’ll be sure to post a follow-up.  Thanks again.

John Mansfield

Probably the clipboard is wiped out by the VBA statements being executed. Instead, build an array of the same size as the range being tested, that stores the original format information.