Conditional formatting holidays on a calendar from a list

[JLawrenc]

I have a calendar setup month to month on one sheet. So like C5 is 1-1-13 and D5 is 1-2-13 then B6 starts the next week (Sorry I couldn't get the table insert to work for me here). I also have the display only show the day of the month. Then on sheet 2 I have a list of holiday dates, is there a way I can have a vlookup in sheet 1 and if a date matches on sheet 2 it has a different background?

 

[BrianMH]

Yes you can use conditional formatting and use a formula.

If you name your holiday date range as Holidays this will be easier.

Then the conditional formatting formula (assuming you are putting the conditional formatting on A1) would be
=COUNTIF(Holidays,A1)>0
note the lack of $

Once you've done 1 cell you can copy and paste special formatting to the rest.

 

[JLawrenc]

Thanks, I do have my holiday range named holidays, if I do =COUNTIF(Holidays,A1)>0 would I have to do that for each of the 365 cells or, I've named all my days as days can I do something like =COUNTIF(Holidays,days)>0. I ask because I also will be adding a vacation function. So if I decided to take next Wednesday off I can just add the date to the vaca list and the calendar will be updated.

 

[BrianMH]

Just do it on a1 or whatever the first cell you want to do this on and make sure to change the reference then copy and paste formatting to the rest of the cells. If you compare all the days it will highlight all the cells. You can do another conditional format for the vacation list.