Conditional Formatting and Filtered Rows

[LAAdams17]

When using conditional formatting to separate sections of a table, for example when DATE in Column A changes from one day to the next (conditional formatting rule description =$A2 <> $A1), how does one get around the issue when using filters and the first 'TRUE' instance of =$A2 <> $A1 is a hidden row so the separator is not seen? Is there some way to just compare visible (unhidden) rows? Thank you.

 

[RJPotts]

The conditional formatting rule in cell A2 (I'm assuming you have a header in A1 and that the list is in chronological order, oldest date in A2, most recent date at the bottom) would be;

=SUBTOTAL(4,$A$1:A1)<>A2

Best regards
Richard

 

[RJPotts]

The conditional formatting rule in cell A2 (I'm assuming you have a header in A1 and that the list is in chronological order, oldest date in A2, most recent date at the bottom) would be;

=SUBTOTAL(4,$A$1:A1)<>A2

Best regards
Richard

Did that work???

 

[LAAdams17]

I don't believe your solution would account for the hidden rows. Here's the solution that worked: =SUMPRODUCT(--(B$2:B3=B3),(SUBTOTAL(103,OFFSET(B3,ROW(B$2:B3)-ROW(B3),,))))=1. I needed to use this b/c I decided I needed to use a text column instead of date column. Thank you for your assistance.

 

[RJPotts]

I don't believe your solution would account for the hidden rows. Here's the solution that worked: =SUMPRODUCT(--(B$2:B3=B3),(SUBTOTAL(103,OFFSET(B3,ROW(B$2:B3)-ROW(B3),,))))=1. I needed to use this b/c I decided I needed to use a text column instead of date column. Thank you for your assistance.

If I read your original question (& criteria) correctly, you wanted the first occurrence of each new date to be highlighted, yes? Where that first occurrence was hidden by a filter you wanted the first visible occurrence of the new date to be highlighted?

Best regards
Richard