+ Reply to Thread
Results 1 to 9 of 9

Excel 2007 : Due Date Alert with Conditonal Formatting

  1. #1
    Registered User
    Join Date
    04-19-2011
    Location
    ny
    MS-Off Ver
    Excel 2007
    Posts
    10

    Due Date Alert with Conditonal Formatting

    I have excel 2007

    I have two columns one is "from" Date and the other is "thru" Date. I need the "thru" column dates to be compared to todays date and
    - colored Green if it is 90 days to being due
    - colored yellow if it is 60 days to being due
    - colored red if it is days to being due

    How do I apply all there conditions to a coloumn with 100 plus dates?

  2. #2
    Valued Forum Contributor tlafferty's Avatar
    Join Date
    04-08-2011
    Location
    United States, Tacoma, WA
    MS-Off Ver
    Excel 2010, Excel 2013 Customer Preview
    Posts
    1,112

    Re: Due Date Alert with Conditonal Formatting

    use conditional formatting:
    =A1=TODAY()-90===>FORMAT GREEN
    =A1=TODAY()-60===>FORMAT YELLOW
    =A1=TODAY()-30===>FORMAT RED

  3. #3
    Registered User
    Join Date
    04-19-2011
    Location
    ny
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Due Date Alert with Conditonal Formatting

    when i put the formula in do i highlight the whole column of dates or one at a time?

  4. #4
    Valued Forum Contributor tlafferty's Avatar
    Join Date
    04-08-2011
    Location
    United States, Tacoma, WA
    MS-Off Ver
    Excel 2010, Excel 2013 Customer Preview
    Posts
    1,112

    Re: Due Date Alert with Conditonal Formatting

    You should be able to select the whole column of dates, just make sure that you remove the $ signs from the references.

  5. #5
    Registered User
    Join Date
    04-19-2011
    Location
    ny
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Due Date Alert with Conditonal Formatting

    It's not working for me =(

    I attatched the worksheet.. the column called "thru" is the one i need highlighted with the conditions.. what am i doing wrong?
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor tlafferty's Avatar
    Join Date
    04-08-2011
    Location
    United States, Tacoma, WA
    MS-Off Ver
    Excel 2010, Excel 2013 Customer Preview
    Posts
    1,112

    Re: Due Date Alert with Conditonal Formatting

    See if the attached does the trick for you.
    Last edited by tlafferty; 04-26-2011 at 08:05 PM.

  7. #7
    Registered User
    Join Date
    04-20-2011
    Location
    Aarhus, Denmark
    MS-Off Ver
    Excel 2013
    Posts
    54

    Re: Due Date Alert with Conditonal Formatting

    Hi mate,

    I have setup the sheet for you with my suggestion for a solution to your query.

    For the color RED which were the dates expired compared to todays date I used the following code:

    Please Login or Register  to view this content.
    The next rule you have to set up if the above rule couldn't be applied is the yellow color which were dates about to expire within 2 month (or 60 days). I used the following code:

    Please Login or Register  to view this content.
    The last rule you wanted to have setup was the green color. Here you simply change the last mentioned code to -3 which is equal to 90 days.

    Since the rules are applied in order you have to make sure the red is first, yellow 2nd etc.

    Please also see my attachment.

    Regards,

    Kagesen
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    11-24-2012
    Location
    United States
    MS-Off Ver
    Mac Excel 2011
    Posts
    64

    Re: Excel 2007 : Due Date Alert with Conditonal Formatting

    What if I wanted the triggers only to happen say 5days, 10 days, 15 days. But I want my date to change at each trigger then I would setup the conditional formatting to change when each date was close to being reach and equal to the amount of days.

  9. #9
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Excel 2007 : Due Date Alert with Conditonal Formatting

    DON_BLACK,

    Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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