How to search Excel conditional formatting formula with specific text

2

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

Gerry

Posted 2014-06-27T05:53:47.540

Reputation: 81

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 – 2016-03-25T14:10:38.597

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 – 2016-03-27T23:52:09.097

Answers

0

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.

bvaughn

Posted 2014-06-27T05:53:47.540

Reputation: 733

Asked: 2014-06-27T05:53:47.540

Viewed: 742 times

Active: 2016-03-29T16:44:28.420