Highlighting if NOT a Specific Set of Dates

Dais Helper

New Member
Joined
Jun 11, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
On Sheet A, select Column B, click Conditional Formatting > New Rule > Use a formula > and enter:

=ISNUMBER(B1)*(COUNTIF(SheetB!A:A,B1)=0)
 
Upvote 0
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! ? ? ?
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top