How to search within conditional formatting rules?

Question

When I need to delete some cells , I have to make sure that cells is not used by any formulas on other cells - otherwise it will break. This is can be done using find and replace tool but I'm having problem when checking whether the cells are used by any rules in the conditional formatting. It's annoying while I have so many conditional formatting where I have to open them one by one. Is there any better way to do it ? maybe like using google script to collect the codes from the conditional formatting rules ?

Answer 1

That would require a script of some complexity, unless all conditional formatting rules in the sheet are strictly limited to applying to one cell only, or only use absolute cell references, which is seldom the case.

Many types of conditional formatting rules can include cell references: custom formula rules, "is equal to" rules, "text contains" rules, "date is before" rules, "is not between" rules, and so on.

Most often, conditional formatting rules apply to a larger range of cells, such as a whole column. They may also apply to several noncontiguous ranges. An A1 reference in a rule is always relative to the top left cell of all the ranges the rule applies to. For this reason, it is more likely than not that the A1 notation of the cell you are interested in won't be explicitly mentioned in the rule, so a simple regex won't cut it in the general case.

The task is further complicated by rules that employ offset(), indirect(), match(), filter() and other such functions.

To implement a rudimentary script for what you are asking:

1. Get all rules in the sheet through Sheet.getConditionalFormatRules().

2. For each rule, get its parameters through ConditionalFormatRule.getBooleanCondition() and BooleanCondition.getCriteriaValues().

3. Locate any A1 type range references in the rule.

4. Get the ranges the rule applies to through ConditionalFormatRule.getRanges().

5. Iterate through the ranges, offsetting those A1 type range references by their vertical and horizontal distance from the top left cell of the ranges the rule applies to, taking possible absolute and relative addressing modes into account.

6. Determine whether the cell you are interested in intersects with the offset range. You can do that with the getRangeIntersection_() utility function.

If you are using gradients, you will additionally have to use ConditionalFormatRule.getGradientCondition().