+ Reply to Thread
Results 1 to 7 of 7

Highlight column "due date" when passed but not if column "status" says "complete"

  1. #1
    Registered User
    Join Date
    09-30-2015
    Location
    Detroit, Michigan
    MS-Off Ver
    2010
    Posts
    7

    Highlight column "due date" when passed but not if column "status" says "complete"

    Hello,

    I’m having trouble using conditional formatting to highlight cells in my excel attachment.

    I want to highlight cells in column B Due Date, when the due date has passed.
    However, ONLY if Column C does NOT also equal “complete” .
    I want to highlight passed due items, but not if they are already done, because then it doesn’t really matter.
    I tried this formula under conditional formatting> new rule> use a formula…

    =AND(B:B[less than sign]TODAY(),C:C[less than greater than facing each other]"Complete")

    However, nothing highlighted, even when cells like B2 should have because it’s "due date" has passed and it's not “complete”.

    Sorry about the brackets. It won't let me post the formula with the signs saying that it's incorrect HTML code. I attached a snip of the formula with the correct signs.

    Thanks,

    Hannah
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by HannahWSU; 07-16-2017 at 10:48 AM. Reason: clarity

  2. #2
    Registered User
    Join Date
    09-30-2015
    Location
    Detroit, Michigan
    MS-Off Ver
    2010
    Posts
    7

    Re: Conditional formatting formula problem

    the formula is also in the yellow cell in the attachment
    Last edited by HannahWSU; 07-15-2017 at 10:19 PM. Reason: clarity

  3. #3
    Registered User
    Join Date
    09-30-2015
    Location
    Detroit, Michigan
    MS-Off Ver
    2010
    Posts
    7

    Re: Highlight column "due date" when passed but not if column "status" says "complete"

    bump no response

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,376

    Re: Highlight column "due date" when passed but not if column "status" says "complete"

    Attach a sample workbook (not image).

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

    Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,194

    Re: Highlight column "due date" when passed but not if column "status" says "complete"

    Quote Originally Posted by HannahWSU View Post
    bump no response
    No response because there was no sample workbook attached ...
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  6. #6
    Registered User
    Join Date
    09-30-2015
    Location
    Detroit, Michigan
    MS-Off Ver
    2010
    Posts
    7

    Re: Highlight column "due date" when passed but not if column "status" says "complete"

    Sorry, my mistake for leaving the workbook off. I have now attached it. Thank you for still taking the time to look at my post and let me know.

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,376

    Re: Highlight column "due date" when passed but not if column "status" says "complete"

    Click on C2

    CF

    ..formula

    =AND($B2 < TODAY(),$C2 <> "Complete")

    Fill==OK

    Applies to: $C$2:$C$100 Or whatever your range is)
    Attached Files Attached Files

+ 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 formula problem
    By rajeshn_in in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-23-2016, 08:41 AM
  2. Conditional formatting problem- not all cells seem to use the formula
    By RattlerSkin in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-02-2016, 09:34 AM
  3. [SOLVED] Conditional Formatting formula for shading problem
    By johnsonbj22 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-25-2015, 12:58 PM
  4. [SOLVED] Conditional Formatting - Formula Problem?
    By KenMcN in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-27-2014, 03:00 PM
  5. [SOLVED] formula problem w/ conditional formatting
    By edwinculanding in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-30-2013, 05:20 AM
  6. Conditional Formatting using formula needed for the following problem
    By Deepthik in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-29-2010, 09:41 AM
  7. Conditional Formatting formula problem
    By ddub25 in forum Excel General
    Replies: 3
    Last Post: 01-21-2010, 08:24 AM

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