+ Reply to Thread
Results 1 to 7 of 7

Conditional formatting - Highlight cell rules

  1. #1
    Registered User
    Join Date
    09-06-2020
    Location
    Richmond, VA
    MS-Off Ver
    Excel for Mac 2016
    Posts
    12

    Question Conditional formatting - Highlight cell rules

    Hello, I am trying to highlight a cell called "Job Date," if the date is between "Date of Visit," and "+45days." For example, I went to visit Customer A on 9/1/20 and got a job on 10/20/20, and went to visit Customer B on 9/15/20, and got a job from them on 10/20/20 as well. I would like to highlight the job date if it's within 45 days of my visit. My columns look like this

    Date of Visit +45days Job Date
    9/1/20 10/16/20 10/20/20
    9/15/20 10/30/20 10/20/20

    I am able to highlight one cell, but can't don't know how to copy that through the "Job Date" column since the comparison dates keep changing. Thanks in advance.

  2. #2
    Valued Forum Contributor
    Join Date
    04-24-2020
    Location
    Woodbridge, VA
    MS-Off Ver
    2016
    Posts
    434

    Re: Conditional formatting - Highlight cell rules

    Hi - can you please attach your file?

  3. #3
    Registered User
    Join Date
    09-06-2020
    Location
    Richmond, VA
    MS-Off Ver
    Excel for Mac 2016
    Posts
    12

    Re: Conditional formatting - Highlight cell rules

    Please see attached
    Attached Files Attached Files

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Conditional formatting - Highlight cell rules

    Try this for the CF in column D.

    =AND(N($D2),$D2>=$B2,$D2<=$C2)
    HTH
    Regards, Jeff

  5. #5
    Registered User
    Join Date
    09-06-2020
    Location
    Richmond, VA
    MS-Off Ver
    Excel for Mac 2016
    Posts
    12

    Re: Conditional formatting - Highlight cell rules

    Thanks, Jeff. That did work. However, what I realized is something more complex. I am attaching the new file for your reference. What I'm trying to get to is to see if I get a job from a customer today, has my sales rep visited them in the past 45 days. So I need to compare the jobs I got today to all the visits my sales rep has done in the last 45 days. So
    1) the customer search needs to be compared first to see if he's even visited them, and
    2) if he has, then need to find out if the date is within 45 days

    Thanks in advance for your help with this!

  6. #6
    Registered User
    Join Date
    09-06-2020
    Location
    Richmond, VA
    MS-Off Ver
    Excel for Mac 2016
    Posts
    12

    Re: Conditional formatting - Highlight cell rules

    See attached file
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    09-06-2020
    Location
    Richmond, VA
    MS-Off Ver
    Excel for Mac 2016
    Posts
    12

    Re: Conditional formatting - Highlight cell rules

    This is the best I could come up with. It works to check against the customer, but doesn't correctly look for the dates
    =IF(AND(COUNTIF($A$2:$A$5,$E2), AND(N($F2),$F2>=$B2,$F2<=$C2)), "Pay", "Don't Pay")

+ 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. Replies: 3
    Last Post: 08-01-2019, 01:35 PM
  2. Conditional formatting - various rules in one cell
    By Lorna B in forum Excel General
    Replies: 9
    Last Post: 09-23-2017, 10:10 AM
  3. Replies: 3
    Last Post: 10-24-2016, 01:25 PM
  4. [SOLVED] Issue with cell fill using conditional formatting rules
    By VB_kid in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-08-2016, 11:34 AM
  5. conditional formatting rules for a cell on sheet 1 based on rules from sheet 2
    By jsard in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 08-15-2013, 09:22 AM
  6. Replies: 1
    Last Post: 06-21-2013, 04:20 PM
  7. [SOLVED] Conditional Formatting - Highlight Cells Rules "text that contains"
    By ggentry in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-11-2013, 07:53 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