Conditional Formatting - Applies To

[polarisking]

My conditional formatting (CF) is used on a dataset that's fluid - rows being added/deleted. I've created a named range using OFFSET to define the dataset dynamically, but after I use it in the "applies to" area of the CF rule, when I come back in it's been resolved to a static range (which defeats the purpose).


So, without presupposing how to do it, how can I define the "applies to" area so I don't have to keep changing it each time the dataset grows/shrinks.


Thanks, in advance.

 

[Luke M]

Doesn't look like XL is able to do that in the way you describe.
Alternatives are to change the CF rule to some sort of AND logic (so, apply CF to entire column AND check somehow if cell should be included in range), or use a VB event to rewrite the applies range (which I wouldn't recommend, as it gets a little complicated and will slow things down).

http://www.mrexcel.com/forum/excel-questions/169039-conditional-formatting-dynamic-named-range.html

 

[bobhc]

Good day polarisking

Have a look at the upload, works if you turn your data in to a table and apply rule to a range.

As you now have a table you can do many things on filtering with the colours