Highlight row if column "D" contains year < 1/1/2023, organized subtotals...

[cweaver]

Hi all,

I'm working with an Excel that I've organized into subtotals (thank you to Mr Excel for that!). I need a formula for doing the following:

1. I'd like to highlight the row (customer name) if:

- Column "D" (invoice date) for that customer contains a year less than 1/1/2023,
- and the customer should not contain any dates in 2023.

Here's some sample data:

 

[Joe4]

OK, do the following:

1. Remove all the Conditional Formatting Rules you currently have in there.
2. Select the range A2:D9002
3. Go to Conditional Formatting and enter the following formula, exactly as-is:

=AND($D1<>"",YEAR($D2)<2023,COUNTIFS($A$2:$A$9002,$A2,$D$2:$D$9002,">="&DATEVALUE("1/1/2023"))=0)

4. Choose your desired CF format/color.

 

[cweaver]

I got it to work!...Thank you! Is there some way to also have highlighted the subtotal roll-up Total, so that I don't have to expand the selections to see the color?

 

[Joe4]

I got it to work!...Thank you! Is there some way to also have highlighted the subtotal roll-up so that I don't have to expand the selections to see the color?

You are welcome.
That is a slightly different question than the original one asked, and may warrant a new thread.
The answer will probably be quite a bit different.

 

[cweaver]

understood thank you.