+ Reply to Thread
Results 1 to 7 of 7

Change colour of cell relative to date. (Passed-Green, Today-Orange Future-Green)

  1. #1
    Registered User
    Join Date
    08-23-2012
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    4

    Change colour of cell relative to date. (Passed-Green, Today-Orange Future-Green)

    I would like to know how to get a cell with a date in it to change colour relative to the real date. I need the cells to be green if they are in the past, orange if todays date and red if they are in the future.

    Thanks in advance!

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Change colour of cell relative to date. (Passed-Green, Today-Orange Future-Green)

    HI lukefallon,

    Welcome to the forum.

    You can use below logics in conditional formatting formula:-

    =B3<TODAY() for Green
    =B3=TODAY() for orange
    =B3>TODAY() for Red

    See attached; change cell colour.xlsx


    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Forum Contributor
    Join Date
    08-09-2012
    Location
    Anch, Alaska
    MS-Off Ver
    2003, 2010, 2013
    Posts
    131

    Re: Change colour of cell relative to date. (Passed-Green, Today-Orange Future-Green)

    Sounds like Conditional Formatting is what your looking for.

  4. #4
    Registered User
    Join Date
    08-23-2012
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Change colour of cell relative to date. (Passed-Green, Today-Orange Future-Green)

    Which rule type is it? I can not seem to work out which one it is?

  5. #5
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Change colour of cell relative to date. (Passed-Green, Today-Orange Future-Green)

    HI lukefallon,

    It is formula usage (using a formula to determine..) in conditional formatting.
    You can see the formula if you go there and click on Edit.. thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  6. #6
    Registered User
    Join Date
    08-23-2012
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Change colour of cell relative to date. (Passed-Green, Today-Orange Future-Green)

    ok i am able to change the colour of single cells, now I need to highlight 500 dates in a column and have the same conditional formatting do I use the following formula.

    $U$4:$U$500<TODAY() for Green
    $U$4:$U$500 =TODAY() for Orange
    $U$4:$U$500 >TODAY() for Red

    (Please note that the dates I am trying to change are a result of another date+14days, which i don't think should effect it)
    Last edited by lukefallon; 08-23-2012 at 01:50 AM. Reason: Typo

  7. #7
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Change colour of cell relative to date. (Passed-Green, Today-Orange Future-Green)

    Hi Lukefallon,

    remove the $ sign from the formula in my workbook and copy paste special formats till rows 500... thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1