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?

4/28/2018	4/21/2018	4/14/2018	4/7/2018	3/31/2018	3/24/2018	3/17/2018	3/10/2018
4	6	4	5	6	4	3	4

<tbody>
</tbody>

 

[Marcelo Branco]

Welcome to Mr Excel forum

Dates as dd/mm/yyyy

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

<tbody>
</tbody>

Try
Select A2:H2 being A2 the active cell
Use this formula in CF
=AND(A2=MAX($A2:$H2),COUNTIF($A2:A2,MAX($A2:$H2))=1)
pick the format you want (fill-->yellow in the example)

Hope this helps

M.

 

[Mitch21]

This worked perfectly. Thank you very much.

 

[Mitch21]

How would the formula change if I wanted to highlight only the first instance of the 6? The one that occurred in column E?

 

[Peter_SSs]

How would the formula change if I wanted to highlight only the first instance of the 6? The one that occurred in column E?

You just need to tweak Marcelo's formula slightly

=AND(A2=MAX($A2:$H2),COUNTIF(A2:$H2,MAX($A2:$H2))=1)

 

[Marcelo Branco]

How would the formula change if I wanted to highlight only the first instance of the 6? The one that occurred in column E?

The one in column E is not the first instance, Do you mean the last instance?

EDIT: Peter has already provided a solution to highlight the last instance.

M.

 

[Peter_SSs]

Actually, both formulas could be simplified a bit.
Original problem
=AND(A2=MAX($A2:$H2),COUNTIF($A2:A2,A2)=1)

Latest problem
=AND(A2=MAX($A2:$H2),COUNTIF(A2:$H2,A2)=1)

 

[Peter_SSs]

The one in column E is not the first instance,

I think it is - look at the dates in row 1.

 

[Mitch21]

Actually, both formulas could be simplified a bit.
Original problem
=AND(A2=MAX($A2:$H2),COUNTIF($A2:A2,A2)=1)

Latest problem
=AND(A2=MAX($A2:$H2),COUNTIF(A2:$H2,A2)=1)

Thank you Peter, this worked well!

 

[Marcelo Branco]

I think it is - look at the dates in row 1.

Ah...now i understand

M.