Highlighting if NOT a Specific Set of Dates

Dais Helper

New Member
Joined
Jun 11, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hello. I hope my idea is possible.🤞 (I have my doubts that it's possible, or if it is - it's a tricky thing.)

So I have a list of students in my multi-sheet, multi-conditional-formatted sheet. The sheet also has one VBA coding workbook that helps me highlight the row I have selected. I track multiple aspects in this workbook. The student's data is in a table to support the table styles.

"Sheet A:" Has headers. In column B is their Estimated Graduation Date (we graduate students monthly) Some entries have invalid dates -- whomever created or updated their record guestimated a time for their graduation if something was still unknown about the students scheduling (it gets confusing to me, not my department).

However when we have students with incorrect graduation dates, when we run a report using grad-date ranges, sometimes these "placeholder" dates cause our students to fall between the cracks of those reports because they're never pulled in the first place. Annoying to say the least.

I would like a way to have a date in Sheet A Column B to be highlight in whatever color of my choice -- if it is NOT one of these specific-below-mentioned dates. My preference is to work solely in formulas, but if that is too clunky or unstable - if the VBA instructions are written for a beginner and is simple to follow - I would be willing to try it. I am always apprehensive & confused about coding :confused: (not that I actually understand what's going on in formulas - I don't, but I can poke around and make them work lol). 😁

"Sheet B" Starting on A1, I have a listing of all of our official/valid graduation dates from 2020 - 2024.
02-09-20 Sun
03-08-20 Sun
04-05-20 Sun
05-03-20 Sun
06-07-20 Sun
07-05-20 Sun
08-02-20 Sun
08-30-20 Sun
09-27-20 Sun
10-25-20 Sun
11-22-20 Sun
12-20-20 Sun
02-07-21 Sun
03-07-21 Sun
04-04-21 Sun
05-02-21 Sun
06-06-21 Sun
07-04-21 Sun
08-01-21 Sun
08-29-21 Sun
09-26-21 Sun
10-24-21 Sun
11-21-21 Sun
12-19-21 Sun
02-06-22 Sun
03-06-22 Sun
04-03-22 Sun
05-01-22 Sun
06-05-22 Sun
07-03-22 Sun
07-31-22 Sun
08-28-22 Sun
09-25-22 Sun
10-23-22 Sun
11-20-22 Sun
12-18-22 Sun
02-05-23 Sun
03-05-23 Sun
04-02-23 Sun
04-30-23 Sun
06-04-23 Sun
07-02-23 Sun
07-30-23 Sun
08-27-23 Sun
09-24-23 Sun
10-22-23 Sun
11-19-23 Sun
12-17-23 Sun
02-04-24 Sun
03-03-24 Sun
03-31-24 Sun
04-28-24 Sun
06-02-24 Sun
06-30-24 Sun
07-28-24 Sun
08-25-24 Sun
09-29-24 Sun
10-27-24 Sun
11-24-24 Sun
12-22-24 Sun

So.... Is this even a possible thing? 🤔 🤞


Excel Version: Office 365
Windows: 10 Enterprise

Thank you in advance for reading this. I hope I got all the needed details down for you. If not please forgive me and ask the needed follow-up questions.
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,336
On Sheet A, select Column B, click Conditional Formatting > New Rule > Use a formula > and enter:

=ISNUMBER(B1)*(COUNTIF(SheetB!A:A,B1)=0)
 

Dais Helper

New Member
Joined
Jun 11, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
On Sheet A, select Column B, click Conditional Formatting > New Rule > Use a formula > and enter:

=ISNUMBER(B1)*(COUNTIF(SheetB!A:A,B1)=0)

SWEET! Thank you sooooo much! It totally works! 🙏 🎉 🎉
 

Watch MrExcel Video

Forum statistics

Threads
1,113,931
Messages
5,545,089
Members
410,652
Latest member
Zot
Top