Conditional Formatting

[Tikey]

Hi, thanks for reading this post.

I am using Excel 2019 ( not 365 )

I added a similar post recently but didn’t receive a reply, maybe my post wasn’t very clear or maybe what I was trying to do was not possible, I’m not sure. I have now simplified what I am trying to do, hopefully this is achievable.

I have a list of names with dates and wish to compare another list with this list and highlight the row ( of the new list ) when the dates of any names in the new list are < the earliest date or > greater than the latest date in the old list, it is only the day / month I wish to compare.

Initial list, the names and dates are made up, the actual spreadsheet is much bigger, can be several 000’s of rows.

Name - Earliest date - Latest date

Red - 31 Jan - 15 Sep
Blue - 16 Apr - 25 Jun
Yellow - 17 Jul - 30 Aug
Green - 5 May - 20 Jun
Orange - 30 Jul - 30 Sep

The second list would be similar, but only have one date which will include the year. The order of the names will be random and there will be several instances of the same name. If the date of a name is < the earliest date or > than the latest date, I wish the row to be highlighted.

Example second table with expected results highlighted **

Name - Date

Green - 03 May 2020 **
Yellow - 01 Aug 2020
Red - 25 Sep 2019 **
Blue - 5 May 2018
Green - 02 Jul 2020 **
Orange - 15 Aug 2020

Hopefully I have explained this OK.
I have looked through all the options of conditional formatting and cannot find a way to do this. If anyone had could offer a suggestion I would be grateful.

Thank you – Dave

 

[Fluff]

Duplicate Conditional Formatting ??

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).