Conditional Formatting & UK Bank Holidays

[Duncs]

Afternoon all.

I have a spreadsheet that displays a calendar, covering the months from
October through to March the year after next. I simply enter into it
the month & year on which the calendar is starting, and then the
spreadsheet goes away and populates the dates automatically for me.

It contains conditional formatting to "shade" areas that do not have
dates within the bound of the month.

So, given the month of December '06, the areas marked with an 'x' below
would appear in a grey shade.

Mon x 4 11 18 25 x
Tue x 5 12 19 26 x
Wed x 6 13 20 27 x
Thur x 7 14 21 28 x
Fri 1 8 15 22 29 x
Sat 2 9 16 23 30 x
Sun 3 10 17 24 31 x


Now, my problem lies with Bank Holidays. What I want to do is add
formatting to say, if the day is a UK Bank Holiday, shade the cell in
Red. So, in the above example, the 25 & the 26 would appear in a red
cell.

However, I don't know how to do this. I don't even know if it's
possible. Can someone advise?

Many thanks

Duncs

 

[Bernie Deitrick]

Duncs,

You would need to have a list of Bank Holidays (perhaps, a named range "BankHol") and then use the
CF with the "Formula is" option:

=NOT(ISERROR(MATCH(C1,BankHol,FALSE)))

Change the C1 to match the address of the first cell of your selection when applying the CF.

HTH,
Bernie
MS Excel MVP

 

[Duncs]

Bernie,

Thanks for your reply.

I've tried your suggestion, but I still can't get it to work.

The only thing I can think of, is that I already have two conditional
formats on each cell. Is there a limit to the number of CFs that a
cell can have?

Duncs

 

[Pete_UK]

You can have up to 3 conditional formats applied to a cell, so you are
ok there.

How is your calendar generated? Do you have dates underlying the
display, formatted to show only the day, or do you just increment from
the previous day? The approach that Bernie gave you implies that you
use dates for the Bank Holiday list and for the calendar.

Hope this helps.

Pete

 

[Duncs]

Bernie / Pete,

I don't know what I was doing wrong, but it's working now.

Many thansk for both your help.

Duncs

 

[Pete_UK]

I don't feel I did anything to help, but thanks for feeding back.

Pete