Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts
    I would like to do a couple of things in Excel.

    1. format the font colour of alternate rows in two different colours
    2. When the value of cell N2 is "Complete" I want the whole row to be shaded ( A2 to N2)
    3. Cells D2 E2 have dates in them and I want them to be shaded red if the date is today or later but NOT if the N2 is "Complete"

    Is this possible in Conditional formatting or is there a better option?

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    The attached workbook has three rules. I have captured these in screen shots for you.

    [attachment=85633:Conditio..._example.xlsx]

    [attachment=85634:Rule_1.png] [attachment=85635:Rule_2.png] [attachment=85636:Rule_3.png]

    If you need this example done in an earlier version of Excel then let us know, or see if you can create similar rules yourself.
    Attached Images Attached Images
    Attached Files Attached Files

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I assumed that the formatting should be applied to all rows, not just row 2.

    1. Select columns A:C. A1 should be the active cell.
    Select Tools | Conditional Formatting...
    Select Formula Is from the first dropdown.
    Enter the formula

    =$N1="Complete"

    Click Format...
    Activate the Pattern tab.
    Select the shade you want to use.
    Click OK.
    Click Add >>
    Select Formula Is from the first dropdown.
    Enter the formula

    =MOD(ROW(),2)=0

    Click Format...
    Activate the Pattern tab.
    Select the shade you want to use.
    OK your way out.

    2. Select columns D:E. D1 should be the active cell.
    Select Tools | Conditional Formatting...
    Select Formula Is from the first dropdown.
    Enter the formula

    =$N1="Complete"

    Click Format...
    Activate the Pattern tab.
    Select the shade you want to use.
    Click OK.
    Click Add >>
    Select Formula Is from the first dropdown.
    Enter the formula

    =D1>=TODAY()

    Click Format...
    Activate the Pattern tab.
    Select the shade you want to use.
    Click OK.
    Click Add >>
    Select Formula Is from the first dropdown.
    Enter the formula

    =MOD(ROW(),2)=0

    Click Format...
    Activate the Pattern tab.
    Select the shade you want to use.
    OK your way out.
    (You may want to remove the second condition from D1 and E1)

    3. Select columns F:N. F1 should be the active cell.
    Perform the same steps as under 1.

    4. Select from column O to the right.
    Select Tools | Conditional Formatting...
    Select Formula Is from the first dropdown.
    Enter the formula

    =MOD(ROW(),2)=0

    Click Format...
    Activate the Pattern tab.
    Select the shade you want to use.
    OK your way out.

    See the attached workbook.
    Attached Files Attached Files

  4. #4
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts
    Thankyou both for your replies. This gives me a great starting point and some thinking to do.
    Much appreciated.

Posting Permissions

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