+ Reply to Thread
Results 1 to 4 of 4

Multi-Conditional Conditional Formatting and Dates

  1. #1
    Registered User
    Join Date
    04-29-2015
    Location
    UK
    MS-Off Ver
    2010
    Posts
    33

    Multi-Conditional Conditional Formatting and Dates

    I have a long list of dates on each row:

    1] 26/01/15, 26/02/15, 26/03/15, 26/04/15,26/05/15
    2] 3/02/15, 3/03/15, 3/04/15, 3/05/15, 3/06/15, 3/07/15, 3/08/15, 3/09/15
    3] 19/12/14, 19/01/15, 19/02/15, 19/03/15
    4] 10/06/15, 10/07/15, 10/08/15, 10/09/15, 10/10/15, 10/11/15, 10/12/15, 10/01/16

    And so on.

    I will be going through each row manually (several times a week most likely) to find any dates that have passed, sending out letters to the appropriate contract numbers (each set of dates is linked to a contract number), then deleting the date that has passed. This set of dates are my reminders of when to send out the letters and I cannot have them deleting automatically (if I went on holiday for instance and had to miss a few weeks), so I need them there for me to look through until I delete them myself.

    The problem I am currently having is with my conditional formatting. To make it easier to find the contracts that have a letter due I want the row to highlight if there is a date on it that it before the current date. This bit is working fine. The problem arises when I delete the date and it remains highlighted, I think this is because of how I have written my conditional formatting. It checks all the dates in a row against the current date and if any are less than the current date, it highlights the row... This means when I delete a date it is technically still less than the current date.

    I've tried writing several different multiple criteria Conditional Formats but none seem to be working for me! Could someone lend a hand with a fresh set of ideas??
    Thanks!

    (Sheet 2)
    Attached Files Attached Files

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,461

    Re: Multi-Conditional Conditional Formatting and Dates

    Instead of deleting the passing date, why not to format it as white font?

    CF for column F to GW:

    =F2<TODAY()

    Format font as white colour
    Quang PT

  3. #3
    Registered User
    Join Date
    04-29-2015
    Location
    UK
    MS-Off Ver
    2010
    Posts
    33

    Re: Multi-Conditional Conditional Formatting and Dates

    This is where I may not have explained myself well enough.
    I need to delete the past dates so that the CF will highlight all the rows with dates that need to be looked at (deleted) and then un-highlight them once I delete them (because they've been checked). Hiding the past dates from view will help me see where I am with each contract but it will not help me find which contracts need my attention (bearing in mind this spreadsheet will have 100's of rows once completed).

    If I can have the CF work, highlighting the ones that need my attention, without the need to delete old dates, that would be even better but I don't see

  4. #4
    Registered User
    Join Date
    04-29-2015
    Location
    UK
    MS-Off Ver
    2010
    Posts
    33

    Re: Multi-Conditional Conditional Formatting and Dates

    Solved this by adding in an extra column at the end of the row that uses countif to count any dates in the row before the current date, then conditional formatting on that extra column to highlight any with a number greater than 0.
    A new day and a new way of thinking around a problem!
    Last edited by Mousiefuzz; 05-07-2015 at 04:42 AM. Reason: fix'd

+ 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. multi line conditional formatting
    By coopedup in forum Excel General
    Replies: 2
    Last Post: 12-23-2014, 11:31 AM
  2. [SOLVED] Excel 2007 : Multi Cell Conditional Formatting
    By HangMan in forum Excel General
    Replies: 3
    Last Post: 06-18-2012, 04:20 PM
  3. Multi formula conditional formatting
    By scaffdog845 in forum Excel General
    Replies: 1
    Last Post: 01-25-2010, 03:25 PM
  4. Multi-Conditional Row Deletion w/ Dates
    By Darksanity in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-04-2009, 04:57 PM
  5. Multi conditional formatting
    By Glio in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-11-2008, 06:39 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