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]

Let's assume that you have a list of the holiday dates. Let's make this a named range and called it "Holidays".
Then, you can select AA3:BD3 and apply this Conditional Formatting formula:

=COUNTIF(Holidays,AA$3)>0

and choose your highlighting color.

 

[nparsons75]

Hi, I still cant get it to work, im convinced its me....Please see below and ignore the original cell references.

Holidays has been created in sheet 1 F4:F10

I highlight cells D14:AH21.

I used the formula: =COUNTIF(holidays,D$14)>0

Just does nothing. I changed the format of the fill to RED.

Could it be the way the dates are being set?

 

[Joe4]

I set up the structure you mentioned above, and it worked perfectly for me.

Are your dates in F4:F10 entered as dates or text?
And easy way to tell: what does the follow formula return?
=ISNUMBER(F4)

How are your dates in D14:AH14 entered? Are they entered as dates (numbers) as well (can use same methodology to check)?

Do you actually have any matching dates (holidays) in the range D14:AH14?

 

[nparsons75]

Hi, Thanks for the help here...

The IS formula returns = TRUE

The dates in D14: AH14 are set like this.

In Cell D14 - (=aa15) where aa15 = date, 01/03/2018

In Cell E15 - (=d14+1) and the same all the way along until AH14. The format is date format.

Matching date is 30th March and yes its in list Holidays and in Cell AG14.

 

[philwojo]

Hi, I still cant get it to work, im convinced its me....Please see below and ignore the original cell references.

Holidays has been created in sheet 1 F4:F10

I highlight cells D14:AH21.

I used the formula: =COUNTIF(holidays,D$14)>0

Just does nothing. I changed the format of the fill to RED.

Could it be the way the dates are being set?

Is your sheet with your Holiday's on it called sheet-1 or Holidays? Also, did you name your data range, F4:F10, holidays, if not the formula won't work (I don't think) as it is using the named range.

If you haven't named your range replace "holidays" in the formula with your F4:F10 range and try that.

 

[Joe4]

OK, it appears that I may have misunderstood your original structure.
I thought that you only had dates in your first row (row 14). Are you saying that all rows (14-21) have dates?
If so, then remove the absolute row reference for our CF formula, i.e.
change:

[COLOR=#333333]=COUNTIF(holidays,D$14)>0

to
[/COLOR]

[COLOR=#333333]=COUNTIF(holidays,D14)>0[/COLOR]

 

[nparsons75]

Progress.....brilliant. Thank you so much....

Working, the only thing now is that the only cells highlighted are AG14 and AG15. I need rows AG14 to AG21 highlighted. I cant see why? only reason being that these are the only two cells that contain dates. The ones below dont. I have it working for weekends ok.

Thank you so much again.

 

[Joe4]

OK, now you have me totally confused!!!

What exactly is in range AG16:A21?
Based on our last discussion, I thought we established that there are dates in cells AG14:AG21.
If any of those 8 dates match one of our "Holidays" dates, that particular cell will be highlighted, and no other.
Isn't that what you want to happen?

 

[nparsons75]

So sorry...you think your confused...ha

So,

AG16:AG21 contains just data, numbers symbolising output, no dates. I was hoping that these cells could also light RED or a colour, when the cells AG14:AG15 are highlighted.


Currently I see AG14:AG15 in red.

AG16:AG21 not highlighted in RED.