+ Reply to Thread
Results 1 to 6 of 6

Ignoring Blank Cell w/ Conditional Formatting

  1. #1
    Registered User
    Join Date
    12-07-2009
    Location
    UTAH
    MS-Off Ver
    Excel 2007
    Posts
    14

    Ignoring Blank Cell w/ Conditional Formatting

    I've read dozens of threads on how to have conditional formatting ignore blank cells but I am not understanding how to make it happen myself...

    Please see the sample I have attached (very basic example).

    Logic: Highlight the cells that are >=10 but ignore the blank cells that have functions in them

    What needs to be done for this to happen?

    Thanks,
    Alan
    Attached Files Attached Files
    Last edited by AlanH; 02-04-2010 at 01:09 PM.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Ignoring Blank Cell w/ Conditional Formatting

    Hi Alan,

    Either use the 'Use a formula.....' option rather than the Cell Value option and in B2 set the formula rule as follows.

    Please Login or Register  to view this content.
    then copy this down.

    Alternatively you could add an extra Cell Value rule which doesn't set any format if the cell is blank and make this the first condition and tick the 'Stop If True' option.

    HTH
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Ignoring Blank Cell w/ Conditional Formatting

    Try this formula rule, instead:
    Please Login or Register  to view this content.
    Does that help?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  4. #4
    Registered User
    Join Date
    12-07-2009
    Location
    UTAH
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Ignoring Blank Cell w/ Conditional Formatting

    I just tried both those formulas:

    =AND(B2>=10,B2<>"")
    =MAX(B2)>10

    Unfortunately, both those didn't satisfy the logic I am looking for.

    Richard,

    Wow, I'm not sure why I missed the cell value option of "Blanks." I simply applied that conditional format on top of what I already have and it worked just like I needed it to.

    Fun times with Excel...

    Thanks all,
    Alan
    Last edited by AlanH; 02-04-2010 at 01:10 PM. Reason: Spelling

  5. #5
    Registered User
    Join Date
    07-05-2012
    Location
    Western NYS
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Ignoring Blank Cell w/ Conditional Formatting

    I have a similar situation - the format when the cell has valid values is driven by the icons (traffic lights, 4 of them) but if the cell is blank, I want either nothing to appear or at least gray out the cell, no traffic light at all. I have tried these alternatives - "stop if true", use a formula - but the traffic light still shows up. Can you not mix these "metaphors" with conditional formatting - icons vs. cell format?
    LBRT

  6. #6
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Ignoring Blank Cell w/ Conditional Formatting

    wolfspecter13,

    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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