Conditional Formatting Based on Cell Value

[kumara_faith]

Hi,

I have the following input table:

Book1
	C	D	E
4	Date	Amount	Accumulated Amount
5	12/10/2022	100
6	13/10/2022	100	200
7	14/10/2022	100	200
8	15/10/2022	100	200
9	16/10/2022	100	200
10	17/10/2022	100	200
11	18/10/2022	100	200
Sheet1

Cell Formulas
Range	Formula
E6:E11	E6	=D6+D5

I am trying to build a conditional formatting that will only highlight the latest value in column E when the latest date and amount in column C and column D is entered. In the above example, cell E11 is highlighted because the last updated date is 18/10/2022 in column C and there is an amount in column D. As the user enters the next information in column C and column D, the previous cells should no longer be highlighted and the latest cell in column E should highlighted. Is there a formula I could use in conditional formatting to make this work? I appreciate all the help.

 

[Fluff]

How about

Fluff.xlsm
	C	D	E
4	Date	Amount	Accumulated Amount
5	12/10/2022	100
6	13/10/2022	100	200
7	14/10/2022	100	200
8	15/10/2022	100	200
9	16/10/2022	100	200
10	17/10/2022	100	200
11	18/10/2022	100	200
12
13
14
Update

Cell Formulas
Range	Formula
E6:E11	E6	=D6+D5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E5:E26	Expression	=AND(C5<>"",D5<>"",C6="",D6="")	text	NO

 

[kumara_faith]

Hi Fluff,

Thank you and that worked. Appreciate your patience. If I would like to highlight the whole row instead of just the cells in column E with the same condition above, how do I modify the formula? Thanks.

 

[Fluff]

Like

=AND($C5<>"",$D5<>"",$C6="",$D6="")

 

[kumara_faith]

Hi Fluff,

Thank you and that worked. I appreciate your help and have a great day ahead.

 

[Fluff]

You're welcome & thanks for the feedback.