Forum Discussion
Conditional formatting applied to a named range reverts to an absolute reference?
Conditional Formatting Rules can be applied to ranges referenced by Name, but as soon as the rule is applied, that Name is replaced with an absolute reference to the range. When any changes are made to the Named range, the CF rules rewrite themselves to cover only the data that was present at the time the rule was applied, resulting in duplicated rules applied to segmented ranges and if any copying/pasting/inserting goes on, things quickly devolve into chaos.
What are the rules governing how CF deals with named entities? Converting them all to absolute references defeats the purpose of referencing a range by name - is there a way to override this autocorrect-like behavior?
When I apply a rule to Table2, I want it to apply to all of Table2, now and in the future, not just the data that was there at the instant I created the CF rule.
Thanks!
Sorry but CF is an antiquated pile of junk that Microsoft hesitates to touch*. It is full of weird visual effects that will make your workbook look like a Romany caravan at sunset, but short on practical considerations, such as how to define the 'Applies to' range so that it follows an named range, much less a spilt range. CF has always performed calculations using the array calc. but then it doesn't use it, instead truncating the condition to the top-left value.
Something CF truly excels at is the creation of fragmented and erroneous ranges.
* I do seem to vaguely remember some improvement being made recently. If someone can point me to it that would be useful. I would be delighted to recant some of my criticism.
- Hi Peter did you ever find any new info? I'm still experiencing the issue described by the OP.
