+ Reply to Thread
Results 1 to 10 of 10

Help with adapting a conditinal formatting formula to stop highlights when a cell is blank

  1. 07-08-2019, 06:31 AM #1
    honeyb23
    honeyb23 is offline
    Registered User
    Join Date
    10-22-2014
    Location
    London
    MS-Off Ver
    microsoft Office 2010
    Posts
    12

    Help with adapting a conditinal formatting formula to stop highlights when a cell is blank

    Capture231.PNG


    I need to alter a formula that I am using for conditional formatting in order to highlight a cell when the date info required (Column I) has passed, providing that the status (Column) is not closed or the date initial information received (Column H) is not blank. I am currently using the original formula in the picture:

    original formula.PNG

    Which leaves me with blank columns that are highlighted red, when I tried to alter to take the blanks into account the formula stops working:


    adapted formula.PNG

    Thanks in advance
    Reply With QuoteRegister To Reply
  2. 07-08-2019, 06:35 AM #2
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,734

    Re: Help with adapting a conditinal formatting formula to stop highlights when a cell is b

    You add another rule and place it at the top of the CF rules list:

    =H4=""

    Format as no fill.

    Similar to this thread from this morning: https://www.excelforum.com/excel-for...ard-times.html
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 07-08-2019, 07:21 AM #3
    Aardigspook
    Aardigspook is offline
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Help with adapting a conditinal formatting formula to stop highlights when a cell is b

    Or amend your second formula to check for H4 not being blank:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.
    Reply With QuoteRegister To Reply
  4. 07-08-2019, 08:03 AM #4
    honeyb23
    honeyb23 is offline
    Registered User
    Join Date
    10-22-2014
    Location
    London
    MS-Off Ver
    microsoft Office 2010
    Posts
    12

    Re: Help with adapting a conditinal formatting formula to stop highlights when a cell is b

    Hi Ali I tried, the way that you suggested however nothing changed
    Reply With QuoteRegister To Reply
  5. 07-08-2019, 08:04 AM #5
    honeyb23
    honeyb23 is offline
    Registered User
    Join Date
    10-22-2014
    Location
    London
    MS-Off Ver
    microsoft Office 2010
    Posts
    12

    Re: Help with adapting a conditinal formatting formula to stop highlights when a cell is b

    Quote Originally Posted by Aardigspook View Post
    Or amend your second formula to check for H4 not being blank:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Hi Aardigspook, I tried your suggestion and now the conditional formatting is not highlighting anything.
    Reply With QuoteRegister To Reply
  6. 07-08-2019, 08:04 AM #6
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,734

    Re: Help with adapting a conditinal formatting formula to stop highlights when a cell is b

    OK - then please attach a workbook so that we can see what's going on. On the face of it, it should work - maybe the cells are not really blank?
    Reply With QuoteRegister To Reply
  7. 07-08-2019, 08:20 AM #7
    honeyb23
    honeyb23 is offline
    Registered User
    Join Date
    10-22-2014
    Location
    London
    MS-Off Ver
    microsoft Office 2010
    Posts
    12

    Re: Help with adapting a conditinal formatting formula to stop highlights when a cell is b

    Quote Originally Posted by AliGW View Post
    OK - then please attach a workbook so that we can see what's going on. On the face of it, it should work - maybe the cells are not really blank?
    Hi Ali,

    I have attached.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 07-08-2019, 08:25 AM #8
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,734

    Re: Help with adapting a conditinal formatting formula to stop highlights when a cell is b

    Red:

    =AND(J4 < > "Closed",I4 < TODAY(),I4 < > "")

    Amber:

    =AND(J4 < > "Closed",I4 < TODAY()+3,I4 < > "")

    Remove the spaces I've added - they are to allow the formulae to be posted here.
    Reply With QuoteRegister To Reply
  9. 07-08-2019, 08:55 AM #9
    honeyb23
    honeyb23 is offline
    Registered User
    Join Date
    10-22-2014
    Location
    London
    MS-Off Ver
    microsoft Office 2010
    Posts
    12

    Re: Help with adapting a conditinal formatting formula to stop highlights when a cell is b

    You're an absolute genius, thank you so much!
    Last edited by AliGW; 07-08-2019 at 08:56 AM. Reason: Please don't quote unnecessarily!
    Reply With QuoteRegister To Reply
  10. 07-08-2019, 08:57 AM #10
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,734

    Re: Help with adapting a conditinal formatting formula to stop highlights when a cell is b

    Why, thank you!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Conditinal Formatting with a Formula
    By pmw19800 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-26-2017, 07:14 AM
  2. Conditinal Formatting with a Formula
    By pmw19800 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-26-2017, 09:39 AM
  3. [SOLVED] Adapting to following formula to skip blank cells in range
    By Jamidd1 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-20-2016, 04:26 PM
  4. Formula stop when seeing a blank cell
    By whatsmyname in forum Excel Formulas & Functions
    Replies: 27
    Last Post: 08-05-2010, 06:21 PM
  5. Excel 2007 : Why are several cells highlights at once, How do i stop this?
    By JULIE1983 in forum Excel General
    Replies: 1
    Last Post: 03-06-2010, 06:10 AM
  6. How Do You Make A Formula Stop At A Blank Cell?
    By BZK in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-09-2007, 05:46 PM
  7. [SOLVED] Getting a chart to stop if the formula resutls in a blank cell
    By markag in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-02-2006, 11:05 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1