Conditional formatting to highlights duplicate date from yesterdays date

I wanted to highlight all the data from yesterdays date

When I use this, same date has been highlighted
=IF(COUNTIFS($F:$F,$F1,$G:$G,$G1,$H:$H,$H1)>1, TRUE,FALSE)

Date Number Color
4/17/2024 1 Blue
4/17/2024 2 Yellow
4/17/2024 3 Red
4/17/2024 4 Violet
4/17/2024 5 Blue
4/17/2024 6 Yellow
4/17/2024 7 Red
4/17/2024 8 Violet
4/17/2024 9 Blue
4/18/2024 6 Yellow
4/17/2024 11 Red
4/17/2024 12 Violet
4/17/2024 13 Blue
4/17/2024 14 Yellow
4/17/2024 15 Red
4/17/2024 16 Violet
4/18/2024 17 Blue
4/19/2024 17 Blue
4/19/2024 19 Red
4/19/2024 20 Violet
4/21/2024 21 Blue

These will be the highlighted. Everyday new date and data will be added in the file.
4/17/2024 6 Yellow
4/18/2024 6 Yellow

4/18/2024 17 Blue
4/19/2024 17 Blue

in conditional formatting you only need to use
COUNTIFS($F:$F,$F1,$G:$G,$G1,$H:$H,$H1)>1
however
note sure what the issue is / question

if the date is in column F - no idea if it is , just guessing , then different dates will not be highlighted
$F:$F,$F1,
as this is only looking for the same date

Please see the Yellow Banner at the top of the page explaining how to attach a sample spreadsheet

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.


Here are the instructions, found at the top of the page again

HOW TO ATTACH YOUR SAMPLE WORKBOOK:, Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

This is my sample attachment. I want the 4/17 & 4/18, 6, yellow to be highlighted. As well as the 4/18 & 4/19, 17, blue. As long as the number duplicates. Data or row should be highlighted because it is a duplicate of yesterdays data.

Example. If there is a data on 4/17 & 4/19, it will not be highlighted.. but if the duplicate is on 4/17 & 4/18, both will be highlighted.. so on and so forth..

Please try this CF-rule =COUNTIF($G:$G,$G2)>1 applies to $F$2:$H$999

Originally Posted by dummy777

I want the 4/17 & 4/18, 6, yellow to be highlighted. As well as the 4/18 & 4/19, 17, blue. As long as the number duplicates.
....
Is it possible if it highlights the yesterdays and todays working dates?

I don't understand what you mean, because 4/19 is not today or yesterday, but it should be highlighted??

And now you also introduce the term working date. What do you mean with that?

Because you are talking about today and yesterday, I have more questions?

Do you want to highlight 4/17 & 4/18 with a duplicate number only when it is 4/18?
Do you want to highlight 4/18 & 4/19 with a duplicate number only when it is 4/19?

Please try this CF-rule applies to $F$2:$H$999:

Formula:

Please Login or Register  to view this content.

You are Welcome!

Thanks for the feedback and rep.

Glad to have helped .