+ Reply to Thread
Results 1 to 9 of 9

use conditional formatting to change cell colours based on due dates,but ignore blank cell

  1. #1
    Registered User
    Join Date
    03-25-2014
    Location
    Notingham, England
    MS-Off Ver
    Excel 2007
    Posts
    6

    use conditional formatting to change cell colours based on due dates,but ignore blank cell

    Help!

    I am struggling with conditional formatting when attempting to change cell colours based on due dates, ie. change to yellow if due date is within 30 days, change to red if due date is overdue.

    My efforts to date have been unsuccessful as the blank cells also change colour. I attach a screen shot of the conditional formatting I am attempting to advice you can offer would be gratefully received.

    Thank you,

    Stewart
    Attached Images Attached Images

  2. #2
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: use conditional formatting to change cell colours based on due dates,but ignore blank

    Instead of images, please upload a sample workbook exactly same as your original workbook with enough sample data in it.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  3. #3
    Registered User
    Join Date
    03-25-2014
    Location
    Notingham, England
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: use conditional formatting to change cell colours based on due dates,but ignore blank

    Sorry, now attached.

    Thanks,

    Stewart
    Attached Files Attached Files

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,084

    Re: use conditional formatting to change cell colours based on due dates,but ignore blank

    Not looked at your examples or pictures, but all you need to do is combine conditions with AND.

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  5. #5
    Registered User
    Join Date
    03-25-2014
    Location
    Notingham, England
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: use conditional formatting to change cell colours based on due dates,but ignore blank

    Fantastic, thanks TMS.

  6. #6
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: use conditional formatting to change cell colours based on due dates,but ignore blank

    Make new rules for Conditional Formatting.

    Select C6:N31 --> Conditional Formatting --> New Rule --> Use a formula to determine which cells to format --> in the formula box below "Format values where this formula is true" --> Place the formula given below in the box --> Format (to format as per your choice) --> OK

    For yellow cells......

    Please Login or Register  to view this content.
    For red cells......

    Please Login or Register  to view this content.
    Does this help?

  7. #7
    Registered User
    Join Date
    03-25-2014
    Location
    Notingham, England
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: use conditional formatting to change cell colours based on due dates,but ignore blank

    Thank you Sktneer, consistent advice with TMS's above. Prompt and clear advice from both, much appreciated.

  8. #8
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: use conditional formatting to change cell colours based on due dates,but ignore blank

    You're welcome. Thanks for the feedback and rep.

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,084

    Re: use conditional formatting to change cell colours based on due dates,but ignore blank

    You're welcome. Thanks for the rep.



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Conditional Formatting a blank cell based on another cell's date
    By krista_o in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-01-2014, 04:13 PM
  2. [SOLVED] Conditional Formatting based on calculated future dates (month only, ignore day and year)
    By Nitsirk82 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-27-2014, 06:42 PM
  3. Replies: 5
    Last Post: 12-07-2013, 02:21 PM
  4. Replies: 3
    Last Post: 11-08-2013, 03:10 PM
  5. IF Dates and conditional formatting (cell colours)
    By aluniquen in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-28-2012, 04:32 AM

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