+ Reply to Thread
Results 1 to 5 of 5

Conditional Formatting formula to color one cell based on a date range in another cell

  1. #1
    Registered User
    Join Date
    07-29-2016
    Location
    dallas, tx
    MS-Off Ver
    365
    Posts
    51

    Conditional Formatting formula to color one cell based on a date range in another cell

    My initial effort was to create a formula that would autofill a cell with the same color from another cell. However, all the research I've done says it's not possible without a VB formula. So I've been searching for a formula to enter into Conditional Formatting that would look at cell AC5 and if the date in that cell is >=03/01/2018 and <=05/31/2018, then it would color fill cells AD5 & AE5 with a certain color. I've already got AC5 with it's own conditional format to color one of four colors based on date ranges. However, I need the adjacent cells, AD5, AE5, to be the same color as AC5 also. AD5 and AE5 contain different data and their color it dependent upon AC5.

    I'm just going in circles trying everything I read. Help/suggestions are much appreciated!

  2. #2
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: Conditional Formatting formula to color one cell based on a date range in another cell

    Put the same conditional formatting rule you have in AC5 in AD5 and AE5.
    Ron
    Knowledge is knowing that a tomato and pepper are fruits. Wisdom is knowing whether to put these in a fruit salad

    Kindly

    [1] Use code tags. Place "[code]" before the first line of code and "[/code"]" after the last line of code. Exclude quotation marks
    [2] Mark your post [SOLVED] if it has been answered satisfactorily by editing your original post in advanced mode.
    [3] Thank (using the little scale) those that provided useful help; its nice and its very well appreciated

  3. #3
    Registered User
    Join Date
    07-29-2016
    Location
    dallas, tx
    MS-Off Ver
    365
    Posts
    51

    Re: Conditional Formatting formula to color one cell based on a date range in another cell

    Thank you for your reply. I did try that, but I know I have a syntax error because it does not change the color of the cells. The following is my last attempted formula in the Conditional Formatting for AD5 and AE5 to reference AC5:

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

  4. #4
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: Conditional Formatting formula to color one cell based on a date range in another cell

    Select AD5:AE5
    Go to Home>Conditional Formatting>New Rule
    Select: Use Formula to Determine which cells to format

    In the rule formula, paste this:
    =AND($AC$5>=DATE(2018,3,1),$AC$5<=DATE(2018,5,31))

    Click on Format button. Go to Fill and select your desired color.

    If you want AD5 and AE5 a different color, then don't select them together and do the above for each cell.

    You can also enter your criteria date in any two cells, say A1 and A2, and then change the section of the formatting that says "DATE(YYYY,MM,DD)" to your designated date cells.

  5. #5
    Registered User
    Join Date
    07-29-2016
    Location
    dallas, tx
    MS-Off Ver
    365
    Posts
    51

    Re: Conditional Formatting formula to color one cell based on a date range in another cell

    This worked perfectly! My co-workers and I thank you so much for your help!

+ 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 formatting for date based on cell that has formula
    By BrookieOU in forum Excel General
    Replies: 3
    Last Post: 11-06-2015, 03:31 PM
  2. Replies: 5
    Last Post: 12-31-2013, 01:44 AM
  3. Replies: 5
    Last Post: 12-07-2013, 02:21 PM
  4. Replies: 3
    Last Post: 11-08-2013, 03:10 PM
  5. Replies: 5
    Last Post: 05-10-2012, 10:49 PM
  6. [SOLVED] Conditional Formatting, current cell color follows to diff cell based on content
    By iallenwood in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-08-2012, 04:13 PM
  7. Conditional formatting of one cell based on date range of another cell
    By raggatip in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 08-02-2010, 02:11 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