+ Reply to Thread
Results 1 to 9 of 9

(conditional formatting) color fill whole row based on dates.

  1. #1
    Registered User
    Join Date
    07-02-2012
    Location
    Indiana, USA
    MS-Off Ver
    Excel 2007
    Posts
    25

    Thumbs up (conditional formatting) color fill whole row based on dates.

    The following part is SOLVED:

    In my spreadsheet, one of my columns (H) has specific dates for each row. If that date is less than or equal to todays date I would like to fill that whole row with the color green. If that specific date is in the future (more than todays date), I would like to fill that entire row with the color red.

    My spreadsheet has hundreds of rows with each row having different dates in the H column. With this conditional formatting it well help us find "expired" rows.

    ---------------------------------------------------------------------------------------------------

    The following part is UNSOLVED (newly added):

    QUESTION: Is it possible to have the cells highlighted in yellow if they are in the future, but only 1 year or less (to show upcoming dates)?

    -Note that the following formulas work for the above problem (thanks DBY!): The cells are highlighted green =AND($H2<>"",$H2<=TODAY()). Dates in the future are
    highlighted in red: =$H2>TODAY().

    Thanks in advance!

    Best,
    privatesinn
    Last edited by privatesinn; 08-20-2015 at 02:00 PM.

  2. #2
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: (conditional formatting) color fill whole row if date in row is less than today

    Hello
    Apply the following Conditional Formatting formula to the range/rows (eg.A1:H200) to be formatted:

    For Green:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    and red

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    DBY

  3. #3
    Registered User
    Join Date
    07-02-2012
    Location
    Indiana, USA
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: (conditional formatting) color fill whole row if date in row is less than today

    Thank you DBY! That worked! One more question, however. Is there a way to make it not highlight the rows if they are empty? For example, I may have to keep changing the range as I input more data, because empty cells are automatically highlighted if the range exceeds the amount of cells.

    Thank you again!!

  4. #4
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: (conditional formatting) color fill whole row if date in row is less than today

    Hi
    Yes you can add an AND condition with the green formula:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    DBY

  5. #5
    Registered User
    Join Date
    07-02-2012
    Location
    Indiana, USA
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: (conditional formatting) color fill whole row if date in row is less than today

    That solved it! Your a miracle man! Thank you!!

  6. #6
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: (conditional formatting) color fill whole row if date in row is less than today

    You're welcome. Thanks for the Rep mark.

  7. #7
    Registered User
    Join Date
    07-02-2012
    Location
    Indiana, USA
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: (conditional formatting) color fill whole row based on dates.

    Hello again all! Note, that I have reopened the question with the following:

    QUESTION: Is it possible to have the cells highlighted in yellow if they are in the future, but only 1 year or less (to show upcoming dates)?

    -Note that the following formulas work for the above problem (thanks DBY!): The cells are highlighted green =AND($H2<>"",$H2<=TODAY()). Dates in the future are
    highlighted in red: =$H2>TODAY().

  8. #8
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: (conditional formatting) color fill whole row based on dates.

    Hello again
    If I've understood you correctly, try for Yellow:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    and red:

    Please Login or Register  to view this content.
    You may have to tweak the >= or <= depending on what you want to be inclusive within each criteria.

    DBY

  9. #9
    Registered User
    Join Date
    07-02-2012
    Location
    Indiana, USA
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: (conditional formatting) color fill whole row based on dates.

    Quote Originally Posted by DBY View Post
    Hello again
    If I've understood you correctly, try for Yellow:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    and red:

    Please Login or Register  to view this content.
    You may have to tweak the >= or <= depending on what you want to be inclusive within each criteria.

    DBY
    That did it! Thanks again SO much DBY!!

+ 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] I need to override a color fill set by Conditional Formatting
    By Gene@action in forum Excel General
    Replies: 9
    Last Post: 01-07-2022, 11:19 AM
  2. Color Fill & Conditional Formatting Problem
    By excelarator in forum Excel General
    Replies: 3
    Last Post: 06-11-2015, 05:47 PM
  3. Fill color of cells with conditional formatting for more than 3 criterias
    By Sai Prashanth in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-20-2014, 03:58 PM
  4. Replies: 5
    Last Post: 04-07-2014, 06:02 AM
  5. conditional formatting color fill with text
    By pvsvprasadcivil in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-04-2014, 01:19 AM
  6. Replies: 5
    Last Post: 12-31-2013, 01:44 AM
  7. Conditional formatting based on date - not today's!
    By HarveyDickinson in forum Excel General
    Replies: 1
    Last Post: 09-11-2009, 05:46 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