Identify cells with conditional formatting and the number of conditions in seconds

Identify cells with conditional formatting — and the number of conditions — in seconds

by Kara Soos

Application:

Microsoft Excel 2000/2002/2003/2004/2007

Operating Systems:

Microsoft Windows, Macintosh

Conditional formatting helps you format your worksheet based on criteria you define. But if you go wild with this feature, you’ll find yourself scratching your head over what cells you’ve applied conditional formatting to and how many conditions exist for each cell. Our macro will programmatically add an informative comment to each cell in a selection indicating how many conditions are in place.

To flag cells to which you’ve applied conditional formatting, we’ll:

• Look at a sample workbook with conditional formatting already in place.

• Build a macro that adds a comment only to cells in your selection with conditional formatting — and customize the comment text to indicate how many conditions exist.

• Create another brief macro that deletes all the comments from your selected range so you aren’t stuck with them permanently.

It’s hard to pin down your worksheet’s conditional formatting. If you select a range of cells that don’t have the same conditions, the conditional formatting window opens with three new conditions — as if there aren’t already conditions in place. As you can imagine, this behavior gets confusing. Old conditions sometimes linger after you try to delete them, or you may miss cells that you meant to include in your conditional formatting.

Our macro tries to make sense of your worksheet’s conditional formatting by assigning a comment to each cell in your selection that indicates how many conditions exist in the cell. Then you can identify cells that seem out of place and correct their conditional formatting.

What it looks like

If you open any workbook in which you’ve applied conditional formatting, you’ll probably find discrepancies. We created a sample customer service log that tracks incoming calls and applied several formatting conditions to different data ranges.

One of our conditions changes the text of an entire row to bold red font if the customer gave the lowest customer satisfaction rating for the call. Another condition changes the fill color to yellow in column J if the customer needs a follow-up call, as shown in Figure A.

Problem: Strange, seemingly out-of-the-blue formatting appears in certain rows. We suspect that some of the previous conditional formatting wasn’t completely wiped out when we edited the worksheet last. Let’s find out where the problem is so we can fix it.

Our worksheet has different conditional formatting in various data ranges — and possibly some old conditional formatting that we thought we’d deleted.

Download: You can follow along with our example by downloading and extracting the file cslog.xls from the URL given at the beginning of this article.

&nbs