Conditional Formatting in Excel

Hello, I use Excel 2007. I’m creating a spreadsheet for some guys I look after at work. I manually put in dates that their training expires. I’ve got 10 operators and 10 skills they need training in. The grid is the easy part but what I would like to happen is Out of date, cell turns RED. In date, cell stays Green and say 28 days prior to running out of date cell turns orange. Is this going to be straightforward ?? Any advise WELCOMED

2 thoughts on “Conditional Formatting in Excel

  1. Excel Geek

    Hello there

    Yes you could do this with conditional formatting.

    Highlight the column containing your expiration dates. Then go to Conditional Formatting. Select New Rule. Then select Use Formula to Determine Which Cells to Format.

    First Conditional Format to highlight in amber those dates within 28 days of expiry.

    Enter in the formula box….
    =B2>=NOW()-28
    For my example cell B2 contained the first date. Change this whatever your first cell is. Then select the formatting you want to have if the condition is true. For mine I used an amber background.

    Second Conditional Format to highlight in red those dates that have expired.

    Create a new rule and enter this formula into the box…
    =B2>NOW()
    Then select the formatting you want to have if the condition is true. For mine I used a red background. Finally on this tick the box that says Stop if True.

    Click OK to go back to the spreadsheet. The third element does not need condition. Simply highlight the column with the dates and change the background colour to green. That is your default colour unless any of the two conditions are present that will then automatically change the background colour.

    Hope you find it useful!

    Thanks for writing!

  2. Brigitte Gibbons

    Hey,

    I would like to know how to apply conditional formatting to drop down lists in Excel 2007. I have got around 20 different drop down lists each containing different text. I would like to have a column next to the drop down list that shows an different coloured indicator when a particular option is selected, in this case to show that if the option is selected then our risk will increase. Hope this makes sense. If not, let me know and I will try to clarify further. Thanks.

Leave a Reply

Your email address will not be published. Required fields are marked *