Recalculate conditional formatting after filtering rows

[robertomd]

Hello,
If we have a table with a column of values that have a conditional formatting relative to those values (e.g. red cell for the highest value of the column, green cell for the lowest), and then we filter this list so one of these values is filtered (hidden), the conditional formatting still aplies to ALL the range, instead to the visible range of cells. In this exemple, the red cell is hidden.
Is there a way to change this behaviour, so if the highest value is filtered, I can see a red cell for the highest of the still visible, non filtered, values?

Thanks in advance

 

[Luke M]

You'll want to make use of the SUBTOTAL function, which can ignore hidden rows. The equivalent of taking the MAX of a range would be something like:
=SUBTOTAL(4,A1:A10)

So, your CF rule would probably look like:
=A2=SUBTOTAL(4,$A$2:$A$100)
for the max, and
=A2=SUBTOTAL(5,$A$2:$A$100)
for the min

 

[robertomd]

Yep, that did the trick!!! Many thanks!