+ Reply to Thread
Results 1 to 17 of 17

Highlight cells based on deadline to ETA

  1. #1
    Registered User
    Join Date
    03-08-2013
    Location
    South Carolina
    MS-Off Ver
    Excel 2007
    Posts
    19

    Highlight cells based on deadline to ETA

    I hope this can be done through conditional formatting because I am not familiar with macros.

    Column A - order #
    Column B - shipping method (this will only be air, sea, or blank; I get this through VLookup)
    Column C - order ETA

    I want to highlight Column C..... if Column B=Air highlight if ETA is in 2 weeks or less; if Column B=Sea or is blank highlight if ETA is in 5 weeks or less.

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Highlight cells based on deadline to ETA

    Have a look at the attached. Is this any help to you?
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    03-08-2013
    Location
    South Carolina
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Highlight cells based on deadline to ETA

    It did not work. Could it be because column B has a VLookup formula?

  4. #4
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Highlight cells based on deadline to ETA

    I just tried with the values in column B populated via a VLOOKUP and it works fine for me.

    Perhaps if you could post an example workbook (desensitise any sensitive information) and we can have a go at solving it for you.

  5. #5
    Registered User
    Join Date
    03-08-2013
    Location
    South Carolina
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Highlight cells based on deadline to ETA

    Here's the spreadsheet. I left the blank rows just in case that would have caused the issue.

    Thank you!!
    Attached Files Attached Files

  6. #6
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Highlight cells based on deadline to ETA

    Is this what you need?
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    03-08-2013
    Location
    South Carolina
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Highlight cells based on deadline to ETA

    When i enter it everything turns red/green. Is it because it's referencing specific cells (i.e.B4, C4)?

  8. #8
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Highlight cells based on deadline to ETA

    In your original post you said "I want to highlight Column C..... if Column B=Air highlight if ETA is in 2 weeks or less; if Column B=Sea or is blank highlight if ETA is in 5 weeks or less."

    So the red is column C highlighting when the first condition you mention is met and the green is column C highlighting when the second condition is met.

    If that's not what you're after then you'll need to explain a little more.

  9. #9
    Registered User
    Join Date
    03-08-2013
    Location
    South Carolina
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Highlight cells based on deadline to ETA

    I apologize for the misunderstanding.

    If the order is being shipped by sea i want that date to be highlighted when it is 5 weeks or less from ETA; if it is air i want it to be highlight when it is 2 weeks or less from ETA.

    I am trying to alert our purchasing department of orders he needs verify that are coming from overseas are shipping on time. Air freight usually takes 1 week & sea freight takes 4 weeks and he needs at least 1 week to alert our vendor(s).

    I will not have access to this over the weekend so if you are going to continue to try and help (which i hope you do) iI will not be able to test or respond until monday.

    Thank you for your help!!!

  10. #10
    Registered User
    Join Date
    03-08-2013
    Location
    South Carolina
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Highlight cells based on deadline to ETA

    Would it be easier to have 1 rule? I do not need the formatting to be different, both can be in the same color.

    Also, the formatting is not working on the last attachment you posted. Order 16939 ETA 4/21/14 did not change even though it is within 35 days.

  11. #11
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Highlight cells based on deadline to ETA

    Try this one. As far as I can fathom, it's doing what you need.
    You can change the conditional formatting colours to be the same, and you could combine the two to be in one formula, but it would just makes things unnecessarily complicated.

    If this still isn't doing what you need I will come back to it.

    BSB.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    03-08-2013
    Location
    South Carolina
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Highlight cells based on deadline to ETA

    Could it be how I'm entering it? I used copy & paste and the only change is column letters.

    =AND(OR(B4="S",B4=""),C4<>"",C4<=TODAY()+35)
    =AND(B4="A",C4<>"",C4<=TODAY()+14)

  13. #13
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Highlight cells based on deadline to ETA

    Quote Originally Posted by krista_o View Post
    Could it be how I'm entering it?
    Hard to tell, because you don't mention what's not working about it now...

  14. #14
    Registered User
    Join Date
    03-08-2013
    Location
    South Carolina
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Highlight cells based on deadline to ETA

    Here's a new copy where I've entered your formula

    KO_03.31.14.xlsx

  15. #15
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Highlight cells based on deadline to ETA

    Amend the conditional formatting formulas so all the 9's are 5's. So B9 should be B5.

  16. #16
    Registered User
    Join Date
    03-08-2013
    Location
    South Carolina
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Highlight cells based on deadline to ETA

    To be completely honest I do not know where the 9's came from but it works now!

    Thank you for your help!

  17. #17
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Highlight cells based on deadline to ETA

    Phew! Got there in the end

    Happy to help.


    BSB.

+ 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] Send email based on deadline date
    By SAsplin in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 06-09-2014, 05:53 PM
  2. [SOLVED] Response deadline based on Business day.
    By mangesh in forum Excel General
    Replies: 3
    Last Post: 04-18-2013, 02:17 AM
  3. Conditioning Formatting based on deadline dates
    By celeliza in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-04-2013, 03:08 AM
  4. Replies: 4
    Last Post: 01-03-2013, 12:25 AM
  5. [SOLVED] Forumla/Formatting Cells Based on Date/Deadline Approaching
    By -Mat- in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-02-2012, 08:29 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