+ Reply to Thread
Results 1 to 6 of 6

Conditional formatting around blank cells

  1. 08-02-2017, 08:28 AM #1
    Mark43
    Mark43 is offline
    Registered User
    Join Date
    08-25-2016
    Location
    USA
    MS-Off Ver
    14.0.7172.5000 (32-bit)
    Posts
    96

    Conditional formatting around blank cells

    I often have a column that has some cells with numbers and others with blanks. My goal is to get certain numbers to stand out and when I conditionally format the blank cells often get colored, which I do not want.

    I searched the internet and found a solution that makes sense but is not working. The attached screenshot illustrates the problem. You can see blank cells colored red.

    The complete first formula is: =ISBLANK(AI4172)=TRUE

    The range of cells I'm trying to format is AI4172:AI8307.

    What's wrong?

    Mark
    Attached Images Attached Images
    Reply With QuoteRegister To Reply
  2. 08-02-2017, 08:36 AM #2
    Special-K
    Special-K is offline
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,076

    Re: Conditional formatting around blank cells

    in a blank cell type this

    =CODE(AI8302)

    It should say #VALUE!
    If it says anything else then your data is not blank.

    If it says 32 then you have spaces in there (which are not equal to blank).
    If it says 160 then your data is web related (which is still not blank).
    In any case if the above does not return #VALUE! then you'll need to manually clear out the cells that look blank (but are not).
    Last edited by Special-K; 08-02-2017 at 08:38 AM.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
    Reply With QuoteRegister To Reply
  3. 08-02-2017, 09:04 AM #3
    Mark43
    Mark43 is offline
    Registered User
    Join Date
    08-25-2016
    Location
    USA
    MS-Off Ver
    14.0.7172.5000 (32-bit)
    Posts
    96

    Re: Conditional formatting around blank cells

    Hi Special-K,

    It did say #VALUE.

    Actually, though, you made me realize the cell actually isn't blank: it has a formula that is outputting ""

    I therefore replaced

    =ISBLANK(AI4172)=TRUE

    with

    =""

    That did not work either.

    Again, I do not want conditional formatting to take place if the cell appears blank.

    Thanks,
    Mark
    Attached Images Attached Images
    Last edited by Mark43; 08-02-2017 at 09:07 AM. Reason: Attachment
    Reply With QuoteRegister To Reply
  4. 08-02-2017, 09:09 AM #4
    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 (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,208

    Re: Conditional formatting around blank cells

    Have you tried:

    =AI4172=""

    =AI4172=0
    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. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  5. 08-02-2017, 09:19 AM #5
    Mark43
    Mark43 is offline
    Registered User
    Join Date
    08-25-2016
    Location
    USA
    MS-Off Ver
    14.0.7172.5000 (32-bit)
    Posts
    96

    Re: Conditional formatting around blank cells

    =AI4172="" works!

    Thank you very much.
    Mark
    Attached Images Attached Images
    Reply With QuoteRegister To Reply
  6. 08-02-2017, 09:29 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 (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,208

    Re: Conditional formatting around blank cells

    You're welcome!

    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. Conditional Formatting - Color Formatting for Blank Cells
    By mlbdc2012 in forum Excel General
    Replies: 10
    Last Post: 02-10-2015, 06:28 AM
  2. Conditional Formatting - how to leave blank cells blank - no colour until date populated
    By leanne2011 in forum Excel General
    Replies: 3
    Last Post: 02-09-2015, 09:00 AM
  3. Conditional Formatting. No formatting for Blank Cells
    By missellyt in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-11-2014, 06:42 AM
  4. [SOLVED] Conditional formatting except blank cells
    By arn0ldas in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-20-2013, 09:31 AM
  5. Conditional formatting. Highlight cells that meet a criteria but ignore blank cells.
    By CEM in forum Excel General
    Replies: 5
    Last Post: 05-18-2013, 07:49 PM
  6. [SOLVED] 3 Outcomes Conditional Formatting based on 4 blank/non blank cells
    By Supraman in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-14-2012, 06:21 AM
  7. [SOLVED] using conditional formatting - blank cells
    By SD in forum Excel General
    Replies: 5
    Last Post: 05-09-2006, 10:25 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1