Conditional formatting on visible range?

Question

I have a table with times of actions and I want to highlight rows that are more than 20-ish minutes more than the one above.
The actual number of minutes is less important.

For that I use conditional formatting with the formula:
=F3-F2>0,01766

So far so good.
But now if I apply filter on the table the the formula does not work anymore because it looks at the hidden rows.
Only the first highlight is made by the conditional formatting the other two are done by me.

Is it possible to get conditional formatting to look at the visible rows only?

Answer 1

I was able to come up with a solution only by using a special columns for the filtered index. In my example, i am filtering the data by '1', So i will add '1' in Column L. If you have multiple filters, you can add in the next rows in col L.

Col H checks if cell in Col E exists in the filters list

Col I finds the last row that is not filtered. The formula has to be entered as an Array Formula i.e. enter the formula and press Ctrl Shift Enter

Col J checks if the difference between cells in Col F for the current row and the last not filtered row is between 20 and 21 minutes. Conditional formatting is applied for this cell being true

Below image without applying any filter

After adding '1' as filter

Filtered rows hidden

Formula

Here is a Google Sheet

Answer 2

Note that SUBTOTAL() has options for ignoring hidden rows: this gives you a way to create a formula-based helper column for determining whether or not a row is filtered out....

Note Col C is an Array formula

After filtering for "A" only:

This way you can filter on multiple columns if required.