Conditional Format for Public Holidays (Excel 2000)

Topic

New Reply

Hi All

I feel quite clever to have been able to figure out how to shade Weekends on my spreadsheet and I did it on my own! But as usual I want to push the limit a bit further.

How can I shade the public holidays? Any ideas?

Kerry (again!)

Reply | Quote

Replies

You could create a table containing public holidays and expand the formula used in conditional formatting to include those dates.

In the attached workbook, I have created a small range (just as a demo) with some holidays (real or imaginary), and named this range Holidays. Next, I selected C4:AG5, and set the formula for conditional formatting to

=OR(WEEKDAY(C$2,2)>5,NOT(ISERROR(MATCH(C$2,Holidays,0))))

This is the formula for conditional formatting of cell C4. Note that the column reference C is relative, while the row reference $2 is absolute, so that in T5, the formula will use T2. The MATCH formula tries to find the date in the Holidays table. If it is not found, MATCH returns #N/A, so we check whether MATCH does *not* return an error value. This is OR’ed with the weekend day test.

Reply | Quote

Pretty clever Hans.

I have discovered that you cant use a reference to another sheet in conditional formatting. I will be having 12 sheets (one for each month) and would have to put the list of holidays in each one and have to update them all. I cant think of any other way to shortcircuit this than making a reference to a Holiday sheet with all of the dates listed.

Is this what you would recommend?

Kerry

Reply | Quote

You are incorrect. You CAN use a reference to cells on another sheet with conditional formatting, if it is a DEFINED NAMED range. Defining the name of the range, makes it “act” like it is on the same sheet, allwing you to have one list for ALL the worksheets.

Steve

Reply | Quote

Sorry for my ignorance, but can you enlighten me on how I would do that?

Kerry

Reply | Quote

The simplest way to define a name is to select the range with the holidays, and then type the desired name into the cell address box on the left hand side of the formula bar. See screenshot.

You can also select Insert | Name | Define…

Reply | Quote