+ Reply to Thread
Results 1 to 8 of 8

How to Conditionally Format text with blank cells

  1. #1
    Forum Contributor
    Join Date
    03-11-2018
    Location
    Tampa, Florida
    MS-Off Ver
    365
    Posts
    105

    How to Conditionally Format text with blank cells

    I have a column with, say, 20 rows. 10 of the cells contain the text "Start". 2 of the cells contain the text "Stop". The remaining 8 cells equal to "".

    In another cell (different row and column as above) I have a cell containing "Start".

    I select the 20 cells in the Column, choose "Conditional Formatting" , "Format only cells that contain", "Cell Value not equal to Start", Format fill to yellow.

    The 2 cells that contain "Stop" fill yellow, but also the 8 cells equal to "". I'm not sure why this happens.

    If my desire is to fill only the 2 cell that contain "Stop", how do I fix my formatting?

    Thank you.

  2. #2
    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,929

    Re: How to Conditionally Format text with blank cells

    1. You are asking to apply CF to cells that DONT contain Start - well, cells with "" dont contain Start, do they?

    2. Have you tried swapping this around and applying CF to cells that contain Stop?
    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

  3. #3
    Forum Contributor
    Join Date
    03-11-2018
    Location
    Tampa, Florida
    MS-Off Ver
    365
    Posts
    105

    Re: How to Conditionally Format text with blank cells

    FDibbins, the cells in the 20 row Column are variable as is the reference cell in a different row/column. Solution 2 will only work in the one instance.

  4. #4
    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,647

    Re: How to Conditionally Format text with blank cells

    You'll need a formula for the CF rule. Assuming the range starts at B2:

    =OR(B2<>"Start",B2<>"")

    Apply the CF rule to the range needed (e.g. =$B$2:$B$21).
    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.

  5. #5
    Registered User
    Join Date
    04-10-2019
    Location
    UK
    MS-Off Ver
    2010
    Posts
    10

    Re: How to Conditionally Format text with blank cells

    This is probably not the most practical way but I would just add another conditional formatting and ensure it is above your original one to say if the cell value = 0, then fill the cell white. (Select all 20 cells, choose "Conditional Formatting", "Highlight cells rule equal to", type in 0 and choose a custom format and change the cell background fill colour to white.)

    I have included an image to explain this below.
    Attachment 620127

  6. #6
    Forum Contributor
    Join Date
    03-11-2018
    Location
    Tampa, Florida
    MS-Off Ver
    365
    Posts
    105

    Re: How to Conditionally Format text with blank cells

    Thank you Ali, that is EXACTLY what I needed. I tried many formulas, but never figured out how it was formatted for a range of cells. Surprisingly difficult to resolve via web search!

  7. #7
    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,647

    Re: How to Conditionally Format text with blank cells

    Glad to have helped!

  8. #8
    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,647

    Re: How to Conditionally Format text with blank cells

    Quote Originally Posted by ra2019 View Post
    This is probably not the most practical way but I would just add another conditional formatting and ensure it is above your original one to say if the cell value = 0, then fill the cell white. (Select all 20 cells, choose "Conditional Formatting", "Highlight cells rule equal to", type in 0 and choose a custom format and change the cell background fill colour to white.)

    I have included an image to explain this below.
    Attachment 620127
    No, not practical and unnecessary. You don’t want to add unnecessary CF to any workbook as it is resource intensive.

+ 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. [SOLVED] Conditionally Format Partial Text
    By Anarchus in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-10-2017, 03:53 AM
  2. [SOLVED] How to conditionally format cells if other cells contain specific text
    By ImranBhatti in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-16-2016, 02:52 PM
  3. [SOLVED] VBA to conditionally format a range that includes blank cells
    By The_Snook in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-13-2016, 04:16 PM
  4. [SOLVED] Getting Blank Cells to Conditionally Format
    By heresteve2 in forum Excel General
    Replies: 4
    Last Post: 09-28-2015, 02:56 PM
  5. How to conditionally format cells using partial text
    By Cremorneguy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-26-2014, 11:26 AM
  6. [SOLVED] conditionally format the first value to the right of a blank cell
    By skoehler9 in forum Excel General
    Replies: 10
    Last Post: 09-14-2012, 04:21 PM
  7. [SOLVED] insert text conditionally and format
    By John Sawyer in forum Excel General
    Replies: 3
    Last Post: 08-17-2005, 02:05 PM

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