+ Reply to Thread
Results 1 to 6 of 6

Highlight Cell if BLANK after Date in Reference Cell

  1. #1
    Registered User
    Join Date
    04-26-2016
    Location
    Los Angeles, CA
    MS-Off Ver
    2010
    Posts
    13

    Highlight Cell if BLANK after Date in Reference Cell

    I know that I need to use conditional formatting, but I can't determine the formula to highlight a blank cell IF:

    B1 is blank if there is a date in A1

    B1 is blank 24 hours after date in A1

    B1 is blank 48 hours after date in A1


    THEN:

    B1 turns clear once a date has been entered into B1


    Please help!

    Thank you!

  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,604

    Re: Highlight Cell if BLANK after Date in Reference Cell

    Do you mean that you want 3 different colours for those conditions?

    If so, you will need to set up 3 different rules:

    Select B1 then click on Conditional formatting | New rule | Use a Formula... , then put this formula in the dialogue box:

    =AND(A1<>"",B1="")

    Click on the Format button | Fill tab and choose your colour, then OK your way out.

    Repeat this for the second condition, but use this formula:

    =AND(TODAY()=INT(A1)+1,B1="")

    and choose a second colour. Repeat for the third condition, but use this formula:

    =AND(TODAY()=INT(A1)+2,B1="")

    and choose your third colour.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    04-26-2016
    Location
    Los Angeles, CA
    MS-Off Ver
    2010
    Posts
    13

    Re: Highlight Cell if BLANK after Date in Reference Cell

    This was exactly what I was looking for. I changed the 3rd formula from

    =AND(TODAY()=INT(A1)+2,B1="")

    to

    =AND(TODAY()>=INT(A1)+2,B1="")

    This way it will continue to highlight the 3rd color until B1 has a date entered into the cell.

    I also added the following formula as first in the series so that if cell A1 is blank, B1 will remain clear (with conditional formatting set to 'no fill')

    =$Z3=""


    Conditional formatting rules in order are:

    Clear if blank
    RED if 48 Hours+
    YELLOW if 24 Hours
    GREEN if Same Day


    Thank you, Pete!

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Highlight Cell if BLANK after Date in Reference Cell

    Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
    Thanks.

    Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    01-20-2020
    Location
    St Helens, England
    MS-Off Ver
    2016
    Posts
    4

    Re: Highlight Cell if BLANK after Date in Reference Cell

    Hello Pete,

    I'm looking for a very similar condition format with a slight difference. I have the current time shown using =NOW() and a list of fixed times rota'd in from 21:00 to 05:00. If the cell adjacent to the fixed number, ie the cell next to 21:00 is not filled in by 22:00 I would like it to format red. If the cell next to 22:00 is not filled in by 23:00 I would also like it to format red and so on. Is this possible?

    Thanks

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

    Re: Highlight Cell if BLANK after Date in Reference Cell

    I'm sorry, but if you read through the Forum Rules at the top of the screen you will see this one:

    4. Do not post a new help request in an existing thread.

    which means that you should not attempt to hijack someone else's thread. Instead, you should start your own new thread, with a reference back to this one if you think it is relevant (you won't be able to post a link to it directly until you have accumulated some more posts).

    It would also help if you attached a sample workbook to this new thread - details of how to do this are given in the yellow banner at the top of the screen.

    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. Calculate differnce between 2 dates, but if 1 date cell is blank leave cell blank
    By Vicious00013 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-11-2014, 11:31 PM
  2. Replies: 3
    Last Post: 01-23-2014, 12:19 PM
  3. Highlight Cell if Reference is Blank but Display 0
    By uberathlete in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-04-2014, 11:50 AM
  4. [SOLVED] How to highlight a cell when the sum is greater than another cell reference
    By basketballbraden13 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-11-2013, 06:02 AM
  5. [SOLVED] Highlight cells between 1 reference cell and last filled cell on Row
    By dleeds in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-06-2013, 05:10 PM
  6. Replies: 4
    Last Post: 02-13-2013, 01:18 PM
  7. Replies: 5
    Last Post: 03-23-2011, 07:09 PM

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