Conditional Formatting Formula Inspector

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:

image

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.

image

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

  1. 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.

  2. Thanks for the compliment! Curious, what browser did you use to download the file, and does it work differently if you try another one?

  3. 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.

  4. 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.

  5. 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

If you have a comment or question about the blog post content, please feel free to post it here. If you need help adapting this solution to your own needs, please post in our free help forum.

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