Highlight Holidays in excel table

[nparsons75]

Hi,

I have 31 columns, each representing a day of the month. Below I have 5 rows or so. I need to highlight all columns that are Holidays in the UK calendar. I have managed to get the sheet to highlight weekends, but really struggling to do the same for uk holidays. I have created a seperate sheet that has a list of all uk holidays for 2018.

The row that contains my dates works like this. In the first column =AA3 (AA3 contains the 1st date of the month).

In the second column, =AA3+1 This then gives me the 2nd date.

This seems to work fine for weekends but not sure if it will for holidays as something is preventing me from getting holidays to work.

Thanks in advance.

 

[Joe4]

So, are AG14 and AG15 the same dates or different dates?
If they are different dates, what if one falls on a Holiday and the other does not?

 

[nparsons75]

They are the same dates, just formatted differently. AG14 is formatted: THU (Thursday) and AG15 is formatted: 15 (15th)

 

[Joe4]

So, if there is really only one date, and it is in the top row (14), and you want all the rows 14:21 to be formatted based on the value in row 14, then my ORIGINAL formula should work, the one where Ihad the absolute reference ($) in front of the row number, i.e. $14.

If, when you do that, it is not highlighting all rows between 14-21, then I suspect that you have OTHER Conditional Formatting rules on those rows interfering with this rule.

 

[nparsons75]

Well.....that fixed it....

Your a genius. That has driven me mad most of the day. I have no idea why it works now and didn't before, but who cares, it works perfectly now so thank you very much...

I have other things I need from this table so have posted again....I bet you run a mile when you see them ha..

Thank you again, really appreciated.

 

[Joe4]

You are welcome.