Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Sorry I did try to search but could not find a question similar to what I want.

My Question: Is there any easy way I could search all conditional formatting that has formula with specific text?

I have many conditional formatting in my excel and many of them have "Use a formula to determine which cells to format". I would like to search all those formula with specific text so that I could do something on them.

e.g. Format values where formula is true: =NOT(ONOFF.ON_DESK)

I would like to search all formula with text "ON_DESK". Thanks!

Formula in Conditional Formatting

share|improve this question
Could you provide some more information? Search allows you to cycle through all cells with "ON_DESK" in the formula. What about search makes this not viable? A macro could cycle through all cells and change the format to red. I would not normally use a macro for this as it has side effects. Is this what you were looking for? – bvaughn Mar 25 at 14:10
Hi bvaughn, the thread has been there for a while and thanks for the comment. It seems to me Excel finds dialog does not search the formula in conditional formatting(I will add an image to my original question to explain clearer where it is). But thanks for the hint, I confirm it could be done by vba. I would hope they will extend the search of find dialog some day to cover formula in conditional formatting as well. – Gerry Mar 27 at 23:52

Cells "meta" which includes the formula is generally not exposed to functions for use. While VBA could access any of the "meta" including the formula, having a trigger so every re-calc causes a macro to run is not good.

However, there might be a way depending on what problem you are trying to solve. Either you have the ON_DESK in a separate cell or if it is in a table that you can refer. The base formula for this is =iferror(IF(TB[z]="ON_DESK", TRUE,FALSE),FALSE) This uses a table called TB with a column called Z, if this was named flag, then it could be used with conditional formatting.

=iferror(IF($XEY1="ON_DESK", TRUE,FALSE),FALSE) This uses the XEY column for holding the OnOff information and your core cells would be modified to using the XEY column for the appropriate data. There are many other ways to do this, but basically you must separate out what you want to use as a flag logic in a cell by itself.

I hope this helps. There are other ways to do the structure, it depends on your data and what problem you are looking to solve.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.