I'm building labor tracking spreadsheet that has every day of the year in it. Some of those days are paid vacation days. I would like to highlight the rows of those particular dates (not a range), in order to make certain the hours entered in those rows stand out (like in green, or whatever). I have a list of holiday dates (in long date format). I would like to use that list in order to find those dates in my main date column (also all displayed in long date), and then highlight the rest of that row. Help would be much appreciated

  • If possible share some sample data especially list of holidays you are suppose to match with Date Column to Format, help us to fix the issue. – Rajesh S Feb 5 '19 at 5:14
  • Also, please add a screenshot because it isn't clear what the worksheet looks like. – fixer1234 Feb 5 '19 at 6:04

enter image description here

How it works:

  • Select the list of Holidays, including header (Dates, Check Screen Shot).
  • Press Ctrl+Shift+F3.
  • From Create Names From Selection, check the TOP ROW & finish with Ok.
  • Reach to Conditional Formatting Command and hit New Rule.
  • Write this Formula.


N.B. In the Formula, Holidays is the Name Excel assigned to Holiday's Date list.

  • Apply an appropriate Format & finish with OK.

You find the Dates are now highlighted as matched with list of Holidays (Dates).

Adjust cell reference and the Holidays list as needed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.