Conditional Formatting Formula Inspector

2013-07-16
Ken Puls
Categories: Excel

David Hagar made a comment in my last blog post that I should add a userform to the code that I built there. Since I had to use it on 40 workbooks, and figured that it could come in useful again, I did exactly that. If you’d like to try it, you can download a copy.

It needs to unzipped and installed as an add-in, at which point you’ll end up with a CF Inspector button on the Formulas tab:

That takes you into the userform where you can type your text and click Find. It will search all conditional formatting formulas in the workbook for your text, and return the list.

Once you select and item, it’s Applies To address and the formula will show in the boxes below so you can see them all.

It’s pretty simple, but could be useful since this kind of facility doesn’t exist in Excel by default. Yes, it would be nice if it would do the replace as well, but given the stability issues I mentioned in the last post, I wasn’t going to try it on my workbook at this point. Maybe in future if I added a backup facility first.

Let me know what you think!

8 thoughts on “Conditional Formatting Formula Inspector”

David Hager on July 16, 2013 at 5:00 pm said:

Wow!
hp on July 17, 2013 at 4:04 am said:

I get an icaperror when I try to download the file :
"ICAP: File decompression/decode error; File: XLGCFInspector.zip; Sub File: \XLG CF Inspector.xlam; Vendor: Sophos, Plc.; Engine error code: 0xA004021A; Engine version: 3.45.0; Pattern version: 4.91G.5361897.3095595010; Pattern date: 2013/07/17 09:34:00"

By the way, your site is one of the most useful on Excel.
Ken Puls on July 17, 2013 at 7:41 am said:

Thanks for the compliment! Curious, what browser did you use to download the file, and does it work differently if you try another one?
hp on July 17, 2013 at 11:20 pm said:

I am using ie and chrome and get the same result from both of them.

I will try from an other computer and see if I get the same result.
hp on August 20, 2013 at 5:52 am said:

Just tried it from another PC with the same configuration with the same result.
This seems to come from the configuration and from the "Vendor: Sophos, Plc" string in the error message I suspect the antivirus.
hp on August 20, 2013 at 5:53 am said:

oh I forget, I downloaded it on another location/ othe PC.

It works great.
Ken Puls on August 20, 2013 at 7:24 am said:

Cool stuff, thanks for letting me know!
John on September 3, 2013 at 4:14 am said:

Cool Ken I really needed this. I am finding CF much harder in 2010 than 2003. I haven't figured out why exactly but I think I was in the habit of getting one cell right and then format painting it. This isn't working as we'll for me now so your tool will help me.

Cheers