Conditional Formatting Question

Mitch21

New Member
Joined
May 2, 2018
Messages
14
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/20184/21/20184/14/20184/7/20183/31/20183/24/20183/17/20183/10/2018
46456434

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,393
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.
 
Last edited:

Mitch21

New Member
Joined
May 2, 2018
Messages
14
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,280
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,393
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.
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,280
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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)
 

Mitch21

New Member
Joined
May 2, 2018
Messages
14
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,336
Messages
5,528,104
Members
409,802
Latest member
joeino

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top