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]

 

[Marcelo Branco]

Welcome to Mr Excel forum

Dates as dd/mm/yyyy

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

28/04/2018​

[/TD]
[TD]

21/04/2018​

[/TD]
[TD]

14/04/2018​

[/TD]
[TD]

07/04/2018​

[/TD]
[TD]

31/03/2018​

[/TD]
[TD]

24/03/2018​

[/TD]
[TD]

17/03/2018​

[/TD]
[TD]

10/03/2018​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

4​

[/TD]
[TD="bgcolor: #FFFF00"]

6​

[/TD]
[TD]

4​

[/TD]
[TD]

5​

[/TD]
[TD]

6​

[/TD]
[TD]

4​

[/TD]
[TD]

3​

[/TD]
[TD]

4​

[/TD]
[/TR]
</tbody>[/TABLE]


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.