Trying to add holidays to existing formula for calendar.

Experts,
With the help from Neil_, I was able to complete my crew calendars to auto populate for the year. Attached you will find what I have so far. Using formula,

=OFFSET('Ops Schedule'!$B$29,MATCH(A5,OFFSET('Ops Schedule'!$F$30,0,MATCH(TEXT($A$3,"mmm"),'Ops Schedule'!$G$29:$R$29,0),99,1),0),0,1,1)

The Crew Calendar matches the Ops Schedule for each crew. What I would like to do is add the holidays noted in the Ops Schedule to somehow tag those cells in the Crew Calendars so they get colored in red. Manually, I would represent the holidays as DH or NH RH or H and let conditional formatting look for this. Also, any help on being able to automate Ops Schedule to change as you change the year. For instance, for 2016, Dec 31 ends in cell R49, so for 2017, Jan 1 should start F50. Our work schedule repeats itself every 10 weeks.

Experts, attached is my almost final calendar. What I would like is for the yearly Crew Calendars to look at the holidays in sheet 1 and overwrite the conditional formatting and fill in the day as red. Is this possible with a formula or do I need a macro?

Originally Posted by MrOchoa

Also, any help on being able to automate Ops Schedule to change as you change the year..

Have a look at this, see what you think, it might be a bit too automated for what you need

Change Crew dropdown as needed in M2

Change year in A1 as needed (no dropdown here, this method works for any date from 1/1/1900 to 12/31/9999 inclusive.

Edit:-

It is still a bit slow to process, but I don't think that there is much hope of avoiding this, given that one change (year) means recalculating every formula in the sheet anyway, something I hadn't taken into consideration with my comments in your previous thread.

That said, overuse of OFFSET is still best avoided where possible.