+ Reply to Thread
Results 1 to 4 of 4

conditional formatting: fill cell color based upon =< or > =NOW()

  1. #1
    Forum Contributor
    Join Date
    05-22-2007
    Location
    Laguna Beach, CA
    Posts
    142

    conditional formatting: fill cell color based upon =< or > =NOW()

    I trade the futures markets and have created an xls for back testing results.

    The lines D24 through H54 (1st month) are my input fields.

    I'd like to create a conditional format changing the fill color based upon:

    if B24 <= B1, leave it clear or fill with white
    if B24 > B1 color it light yellow

    and, continue the formula to line 54.

    I'm trying to color these cells once the date has passed and/or I've inserted the results.

    Thanks.

    Bruce
    Attached Files Attached Files
    Last edited by InvGrp2; 01-16-2019 at 01:40 PM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,600

    Re: conditional formatting: fill cell color based upon =< or > =NOW()

    It would be better to use =TODAY() in B1, rather than =NOW(), as the NOW function returns the date and time, whereas TODAY returns just the date. By the same rationale, you might want to change the formula in B5 to =INT(B1), to ensure that it is only a date.

    As regards the conditional formatting, it is better to highlight all the cells B24:H54 before you start, then you can apply all the conditions in one go. Click on CF | New Rule | Use a formula... , then put this formula in the box that pops up:

    =$B24>$B$1

    Note that the cell references here are relative to the first cell in the range (i.e. B24), so you always want to compare the date in column B (hence the $B in the reference) and to compare this with the cell B1 (hence $B$1). Then click on the format button | Fill tab and choose Yellow, then OK your way out.

    Repeat (with the cells still highlighted), but this time use this formula:

    =$B24<=$B$1

    and choose white for the background colour. Excel will automatically adjust the cell references to suit the cells that were highlighted.

    Hope this helps.

    Pete

  3. #3
    Forum Contributor
    Join Date
    05-22-2007
    Location
    Laguna Beach, CA
    Posts
    142

    Re: conditional formatting: fill cell color based upon =< or > =NOW()

    Pete:

    It worked perfectly.

    Thanks.

    Bruce

    P.S. I had most of it but my formula was off...I tired "=IF" in front of the cells.

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,600

    Re: conditional formatting: fill cell color based upon =< or > =NOW()

    Glad to help, Bruce - thanks for the rep.

    Pete

+ 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: 1
    Last Post: 09-09-2017, 01:56 PM
  2. [SOLVED] fill color row based on conditional formatting
    By k1dr0ck in forum Excel General
    Replies: 3
    Last Post: 09-02-2016, 02:56 AM
  3. Conditional formatting color fill based on nested if statement
    By traceya in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 07-05-2016, 01:58 PM
  4. Replies: 21
    Last Post: 12-16-2015, 03:04 PM
  5. [SOLVED] (conditional formatting) color fill whole row based on dates.
    By privatesinn in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-20-2015, 02:00 PM
  6. Copy/paste based on fill color/conditional formatting
    By Wheelie686 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-27-2014, 03:45 PM
  7. Replies: 10
    Last Post: 06-19-2013, 05:28 PM

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