Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    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. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #5
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    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. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •