none
Excel 2010 - Conditional Formatting same condition to multiple cells

    Question

  • Hi

    I want to show in a cell the following:

    if the entered date is:

    before today's date (in the past) = red

    90 days before today's date = orange

    <90 days before today's date = green

    I need to apply this to over 300 rows, can I just select all the cells and then apply those 3 conditions?

    Thanks.

    Viv


    Viv Haig

    Tuesday, November 20, 2012 1:59 PM

Answers

  • You could set green as the default color (in the Format Cells dialog).

    If you prefer to use conditional formatting:

    Format only cells that contain - cell value - greater than or equal to - =TODAY()+90

    Click Format... and specify green.


    Regards, Hans Vogelaar

    • Marked as answer by VHaig Tuesday, November 20, 2012 3:09 PM
    Tuesday, November 20, 2012 2:56 PM
  • Hi

    Assuming entry date is a column, lets say A and you have column headings in row 1, then your formulae will be:

    $A2<TODAY() formatted red

    $A2=TODAY-90 formatted orange

    $A2<TODAY()-90 formatted green

    With dates a date in the past will always be smaller then todays date and future dates larger.  As the date is basically counting the number of days elapse since 1/1/1900.

    If you have the whole data area selected whne apply the conitional formatting then the whole row wiill format.  If you have just the column (A in our eaxmple) highlighted jus the cells in the colummn will highlight and you could dispose of the $ in the formulae above.

    Hope this helps


    G North MCT

    • Marked as answer by VHaig Tuesday, November 20, 2012 3:10 PM
    Tuesday, November 20, 2012 3:03 PM

All replies

  • Yep, select the entire range, then set the conditional formatting rules.

    Regards, Hans Vogelaar

    Tuesday, November 20, 2012 2:12 PM
  • I've nearly got it working, I'm just not sure of the formula for the green condition - any suggestions welcome.

    Viv Haig

    Tuesday, November 20, 2012 2:16 PM
  • That depends on how you have set up the rules for red and orange. Can you tell us how you specified those?


    Regards, Hans Vogelaar

    Tuesday, November 20, 2012 2:43 PM
  • For the red I chose:

    Format only cells that contain - less than - =TODAY()

    For the orange I did the same but with =TODAY()+90

    Thanks, Viv


    Viv Haig

    Tuesday, November 20, 2012 2:47 PM
  • You could set green as the default color (in the Format Cells dialog).

    If you prefer to use conditional formatting:

    Format only cells that contain - cell value - greater than or equal to - =TODAY()+90

    Click Format... and specify green.


    Regards, Hans Vogelaar

    • Marked as answer by VHaig Tuesday, November 20, 2012 3:09 PM
    Tuesday, November 20, 2012 2:56 PM
  • Hi

    Assuming entry date is a column, lets say A and you have column headings in row 1, then your formulae will be:

    $A2<TODAY() formatted red

    $A2=TODAY-90 formatted orange

    $A2<TODAY()-90 formatted green

    With dates a date in the past will always be smaller then todays date and future dates larger.  As the date is basically counting the number of days elapse since 1/1/1900.

    If you have the whole data area selected whne apply the conitional formatting then the whole row wiill format.  If you have just the column (A in our eaxmple) highlighted jus the cells in the colummn will highlight and you could dispose of the $ in the formulae above.

    Hope this helps


    G North MCT

    • Marked as answer by VHaig Tuesday, November 20, 2012 3:10 PM
    Tuesday, November 20, 2012 3:03 PM
  • Many thanks to all the contributors. Problem solved.

    Viv Haig

    Tuesday, November 20, 2012 3:10 PM