Conditional Formatting Question

[Mitch21]

Hello,

I want to conditionally format the highest value in a row. However, I only want to highlight the most recent occurrence of that value. For instance, in the table below, I only want to highlight the 6 that occurred on 4/21/2018, not the one that occurred on 3/31.

When I try to write a formula to conditionally format, it highlights both instances. Can anyone help?

[TABLE="width: 500"]
<tbody>[TR]
[TD]4/28/2018[/TD]
[TD]4/21/2018[/TD]
[TD]4/14/2018[/TD]
[TD]4/7/2018[/TD]
[TD]3/31/2018[/TD]
[TD]3/24/2018[/TD]
[TD]3/17/2018[/TD]
[TD]3/10/2018[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Thank you Peter, this worked well!

Glad you got a satisfactory outcome.

 

[Mitch21]

Can anyone help me understand how I would now also highlight the second highest number in that row a different color? In this circumstance, say I wanted the 5 in row D to be blue?

 

[Peter_SSs]

Can anyone help me understand how I would now also highlight the second highest number in that row a different color? In this circumstance, say I wanted the 5 in row D to be blue?

1. There were two 6's and one 5 in that sample data. Do you only want the 5 highlighted and neither of the 6's?

2. What if, as well as the two 6's, there was three 5's in the range - what, exactly, should be highlighted?

 

[Mitch21]

1. I would want the 6 to remain highlighted yellow, but the 5 to be highlighted blue
2. If there were three 5's, I would want the FIRST 5 to be highlighted, the same that we had originally done with the 6's

 

[Peter_SSs]

OK, try this.

Excel Workbook
	A	B	C	D	E	F	G	H
1	28/04/2018	21/04/2018	14/04/2018	7/04/2018	31/03/2018	24/03/2018	17/03/2018	10/03/2018
2	5	6	4	5	6	4	3	4
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =AND(A2=MAX(IF($A2:$H2	Abc
A2	2. / Formula is =AND(A2=MAX($A2:$H2),COUNTIF(A2:$H2,A2)=1)	Abc

 

[Mitch21]

That is VERY close. I should have been more specific. I would only want a 5 to be highlighted in Blue if it occurred BEFORE the 6 (by date). So in the example you put, none of the 5's would be highlighted. But, if a 5 occurred on 17/03/2018, it would be highlighted. Is that more clear?

 

[Peter_SSs]

Is that more clear?

Yes. Just insert this extra bit into the 'blue' formula

AND(A2=MAX(IF($A2:$H2<MAX($A2:$H2),$A2:$H2)),COUNTIF(A2:$H2,">="&A2)=1)

 

[Mitch21]

Okay, that did the trick. Thank you Peter! You're a life saver

 

[Peter_SSs]

Okay, that did the trick. Thank you Peter! You're a life saver