Thread: Conditional Format for Public Holidays (Excel 2000)

1. Conditional Format for Public Holidays (Excel 2000)

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!)

2. Re: Conditional Format for Public Holidays (Excel 2000)

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,Holiday s,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.

3. Re: Conditional Format for Public Holidays (Excel 2000)

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

4. Re: Conditional Format for Public Holidays (Excel 2000)

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

5. Re: Conditional Format for Public Holidays (Excel 2000)

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

Kerry

6. Re: Conditional Format for Public Holidays (Excel 2000)

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...

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•