+ Reply to Thread
Results 1 to 2 of 2

Conditional Formatting: Don't format blank cells

  1. #1
    Registered User
    Join Date
    08-18-2021
    Location
    UAE
    MS-Off Ver
    365
    Posts
    8

    Conditional Formatting: Don't format blank cells

    I used the below formula for conditional formatting when 3 conditions are met cell should be highlighted with red.

    =IF(AND($L1>=8,$H1="CAR",$M1="Open"),TRUE,FALSE)

    I have used 4 rules based on different categories but here i am facing a problem when any cell is blank it still shows highlighted. My requirement is if any cell is blank how to handle that condition without getting highlighted.

    If all categories Column (H) can fit into one formula it is also fine.

    I have attached the sample file for further explanation.

    Sample File.xlsm

    Thanks.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,721

    Re: Need Formula for Conditional Formating

    You could add a condition to your AND function.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    But because you have four rules you may find it easier to just add one rule to cover this. Add one more rule that has no formatting. You must make it the first rule and check "Stop If True".
    CF.JPG

    By the way, you do not need to use an IF statement to provide a TRUE or FALSE result. The AND function itself already does that. The formula you showed above can be simplified to

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

+ 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. Conditional format based on date - formatting blank cells
    By jimmisavage in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-09-2017, 10:50 AM
  2. [SOLVED] Conditional Formatting - Format A>B unless B=Blank
    By KDSearle in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-22-2017, 12:55 PM
  3. [SOLVED] Conditional formatting - no format if another cell is NOT blank
    By TiffanyHarrison in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-15-2016, 12:57 PM
  4. Replies: 3
    Last Post: 02-09-2015, 09:00 AM
  5. [SOLVED] Conditional formatting - do not format cell if it is blank
    By Glayva in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-28-2013, 01:13 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. Conditional Formatting - blank cell gets unwanted format.
    By cheesysocks in forum Excel General
    Replies: 5
    Last Post: 12-03-2010, 10:56 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1