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

    Conditional Formatting (Excel 97)

    I am looking for help to create a conditional format formula.

    The attached file shows what I am trying to do.

    Kerry

  2. #2
    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 Formatting (Excel 97)

    Highlight Cells K5:O50 (extend the column number and rows as desired to expand the range)
    Format -conditional formatting..
    Select: "Formula is" in left combobox
    Add to right box (No quotes)"
    "=AND(MONTH($G5)=MONTH(K$2),NOT(ISBLANK($G5)),NOT( ISBLANK(K$2)))"
    <format>
    Patteren tab
    Select gray shading <ok><ok>

    The formula looks for a match, but also makes sure that G is not blank (Month = 1 for "blank") as well as nothing in ROw 2.

    Steve

  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 Formatting (Excel 97)

    Thanks Steve. I gave this a go but with no luck. I have attached a the spreadsheet instead of a screenshot.

    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 Formatting (Excel 97)

    You set it as condition 3. Conditons are NOT cumulative. SInce the 1st condition is met, that formatting takes place (no matter what the other conditions have).

    If Condition 1 is false then condition 2 is examined If TRUE that formatting is active.
    IF BOTH condition 1 NAD condition 2 is false then condition 3 is checked.

    You can ONLY have 4 formats:
    Format 1 (Std format via format cells when NO conditions are true)
    Format 2 when CONDITION 1 is TRUE
    Format 3 when CONDITION 1 is FALSE and Condition 2 is TRUE
    Format 4 when CONDITION 1 is FALSE and Condition 2 is FALSE and condition3 is TRUE

    If you want shaded for date AND Green based on col I you would have to AND the 2 conditions:
    =AND($i4<k4,MONTH($G4)=MONTH(K$2),NOT(ISBLANK($G4) ),NOT(ISBLANK(K$2)))

    Then you would need another for shaded and ORANGE (when K is NOT larger than I)
    =AND($i4<k4,MONTH($G4)=MONTH(K$2),NOT(ISBLANK($G4) ),NOT(ISBLANK(K$2)))

    Then for the third (green no shading)
    =$i4<k4

    For orange with no shading you would have to set the format of the cells directly (format -cells)

    If you need MORE than 4 formats with 3 conditions, you will have to have a macro do it (which could slow things down considerably to keep it live and always up-to-date, since the macro would have to run on every worksheet change and reformat EVERYTHING.

    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 Formatting (Excel 97)

    Hi Steve

    Thanks for this info. I wasnt aware of this situation. As it turns out, I really dont need those other conditions anyway. I put in your original formula and it works just fine.

    Thanks again

    Have a good weekend!

Posting Permissions

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