Highlight subtotal

[cweaver]

Is there some way to also highlight the subtotal roll-up Total row, so that I don't have to expand the selection to see the green color?

The excel sheet is subtotaled by customer.

This is the formula being used to highlight rows in green:
=AND($D2<>"",YEAR($D2)<2023,COUNTIFS($A$2:$A$9002,$A2,$D$2:$D$9002,">="&DATEVALUE("1/1/2023"))=0)

 

[Peter_SSs]

This is the formula being used to highlight rows in green:
=AND($D2<>"",YEAR($D2)<2023,COUNTIFS($A$2:$A$9002,$A2,$D$2:$D$9002,">="&DATEVALUE("1/1/2023"))=0)

That is dependant on the date in column D. Since the subtotalled row does not have a date in column D, how would you determine whether of not to highlight the subtotalled row?

 

[cweaver]

Is there a modification to the this formula that could could be made:

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

...the modification would take into account the following:

If the formula is TRUE (and the cells are highlighted in green), then the row above those cells highlighted in green that contain "*** Total" should also be highlighted.

Does that make sense?

 

[Peter_SSs]

Does this do what you want?

cweaver.xlsm
	A	B	C	D
1	Name	Hdr2	Hdr3	Hdr4
2	Grand Total
3	a Total
4	a			3/03/2022
5	a			3/03/2023
6	a			8/08/2024
7	b Total
8	b			5/07/2023
9	b			6/06/2022
10	c Total
11	c	1	6	17/05/2022
12	c	2	7	15/08/2021
13	c	3	8	4/04/2021
14	c	4	9	8/07/2021
15	c	5	10	18/05/2022
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:D15	Expression	=AND(IF(RIGHT($A2,5)="Total",$D3,$D2)<>"",YEAR($D2)<2023,COUNTIFS($A$2:$A$9002,IF(RIGHT($A2,5)="Total",$A3,$A2),$D$2:$D$9002,">="&DATEVALUE("1/1/2023"))=0)	text	NO

When collapsed:

 

[cweaver]

Genius! Thank you!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.