+ Reply to Thread
Results 1 to 2 of 2

Conditional format cells based upon matching cells & date range from today?

  1. #1
    Registered User
    Join Date
    Kettering, OH
    MS-Off Ver

    Conditional format cells based upon matching cells & date range from today?


    Please see attached and see sheets Cycle_Count_Sample & Stock Movement Cycle Count.
    I have a list of all inventory on the Cycle_Count_Sample sheet (most items removed).
    I have a list of inventory that's been cycle counted on the Stock Movement Cycle Count sheet (most cycle counts removed).
    I'm wanting to have conditional formatting highlight all items that have been cycle counted over the past 3 months (91 days), based upon the dates in the Stock Movement Cycle Count sheet. Please see how the highlighting should appear. As you can see, rows 2-11 aren't highlighted due to the item #s not appearing on the Stock Movement Cycle Count sheet, and row 16 isn't highlighted due to the date being greater than 91 days older than today (3/18/22).
    Is this function possible? If so, how?

    Thank you.
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    North Carolina
    MS-Off Ver

    Re: Conditional format cells based upon matching cells & date range from today?

    First of all, I converted everything into an Excel Table. There are too many good reasons NOT to use Excel Tables.

    I added a Control Panel Sheet. It's just a place to park the current date. You can CUT and paste Cell B1 anywhere else. I gave it a name Today_Date. It's a small thing. I'd rather Excel have to calculate the date once rather than the number of times the formulas that use it appear.

    On the cycle count sheet, I added a helper column to determine if the cycle count is within 90 days: =[@Date]>=Today_Date-90 <- this shows one of the advantages of Tables: you can use column headers in the formulas which makes them easier to understand and debug.

    Then on the Stock Level Detail sheet, I have another helper column Current =COUNTIFS(Table_Cycle_Count[Item No.],[@[Item No.]],Table_Cycle_Count[Current],TRUE)>0 This formula counts the number of cycle counts for the part in the past 90 days. If it is greater than zero, the the formula is true.

    I used this value to set the conditional formatting.

    Another advantage of tables is that they copy things down automatically like formulas, validations and formats (including conditional formatting).
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

+ 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 Format Needed to highlight based on matching text in certain cells
    By excelteam777 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-13-2020, 09:17 PM
  2. [SOLVED] How to format range of cells if J4 date is today?
    By Mr.Castle in forum Excel General
    Replies: 5
    Last Post: 09-01-2019, 02:50 AM
  3. Replies: 4
    Last Post: 02-06-2017, 10:20 PM
  4. Replies: 1
    Last Post: 09-30-2012, 03:01 PM
  5. Replies: 3
    Last Post: 09-13-2012, 03:25 AM
  6. Conditional Formating of cells based on today's date, and another date
    By dviolante10 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-24-2012, 02:32 PM
  7. Replies: 3
    Last Post: 12-11-2007, 01:36 PM

Tags for this Thread


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