+ Reply to Thread
Results 1 to 9 of 9

Conditional Format a cell IF one cell is blank AND another cell contains text

  1. #1
    Registered User
    Join Date
    09-28-2018
    Location
    Huntsville, AL
    MS-Off Ver
    Office 2016
    Posts
    25

    Conditional Format a cell IF one cell is blank AND another cell contains text

    I'd like to highlight a cell in Column A IF the corresponding cell in Column G is blank AND corresponding cell in Column C contains "this" OR "that". Partial text should be applied to Column C as other words are in the cell.
    I have tried this formula: =AND($G1="",$C1="this",$C1="that") but it doesn't work. I think my problem is Excel is looking for specific text "this" and returns false if other words are in the cell. I thought maybe an asterisk would help but it doesn't. Thoughts? Thanks!CONDITIONAL FORMAT.png

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,080

    Re: Conditional Format a cell IF one cell is blank AND another cell contains text

    Select A2:G8 & use this
    =AND($G2="",--ISNUMBER(SEARCH("GUD",$C2))+--ISNUMBER(SEARCH("std",$C2))=1)

  3. #3
    Spammer
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365
    Posts
    302

    Re: Conditional Format a cell IF one cell is blank AND another cell contains text

    Hi, to both!

    Another option could be:

    1. Select A2:G8 leaving activecell A2
    2. Use this formula into CF:

    =($G2="")*(COUNTIF($C2,"*GUD*")+COUNTIF($C2,"*STD*"))

    3. Apply Red Font

    Blessings!

  4. #4
    Registered User
    Join Date
    09-28-2018
    Location
    Huntsville, AL
    MS-Off Ver
    Office 2016
    Posts
    25

    Re: Conditional Format a cell IF one cell is blank AND another cell contains text

    Thank you! I adjusted the formula to apply to the whole Column A and took out the =-1 at the end of the formula because it was not making A8 red. Can you explain what the =-1 is used for?
    Attached Images Attached Images
    Last edited by behmen; 09-28-2018 at 12:50 PM. Reason: found solution

  5. #5
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,080

    Re: Conditional Format a cell IF one cell is blank AND another cell contains text

    In your OP you said GUD or STD & showed rows 3,4 & 6 with no highlight, so I assumed that if col C had BOTH GUD & STD then it should not be highlighted.
    This part of the formula
    --ISNUMBER(SEARCH("GUD",$C2))+--ISNUMBER(SEARCH("std",$C2))
    will return a 1 or a 0 for each check, so if the cell contains both values it will return 2. I therefore added the =1 to check that only 1 of the values was present.

  6. #6
    Spammer
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365
    Posts
    302

    Re: Conditional Format a cell IF one cell is blank AND another cell contains text

    behmen... did you try my proposed formula? this works with GUD or STD or BOTH (GUD or STD) are present. Blessings!

  7. #7
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,080

    Re: Conditional Format a cell IF one cell is blank AND another cell contains text

    If you want to highlight col A id col C contains both values, this is a simplified version of my formula
    =AND($G2="",OR(ISNUMBER(SEARCH("GUD",$C2)),ISNUMBER(SEARCH("std",$C2))))

    Thanks for the Rep.
    Last edited by Fluff13; 09-28-2018 at 01:37 PM.

  8. #8
    Registered User
    Join Date
    09-28-2018
    Location
    Huntsville, AL
    MS-Off Ver
    Office 2016
    Posts
    25

    Re: Conditional Format a cell IF one cell is blank AND another cell contains text

    Ahhh, I can see how that would be confusing. I should have left the cell in Column G blank for one of those samples which had both tests and highlighted the sample # in my example.

  9. #9
    Registered User
    Join Date
    09-28-2018
    Location
    Huntsville, AL
    MS-Off Ver
    Office 2016
    Posts
    25

    Re: Conditional Format a cell IF one cell is blank AND another cell contains text

    Johnmpl--- Yes I did, it works! Thanks, now I have a few formulas to play around with and adjust for other needs!
    Last edited by behmen; 09-28-2018 at 02:45 PM.

+ 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 - If cell is blank, then blank other cell
    By Rosco88 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-11-2017, 06:51 AM
  2. Replies: 6
    Last Post: 01-11-2016, 09:04 AM
  3. [SOLVED] Conditional Format If 1 Cell Blank and Another Has Text
    By brent_milne in forum Excel General
    Replies: 3
    Last Post: 08-26-2015, 03:53 PM
  4. Replies: 8
    Last Post: 06-27-2015, 03:21 PM
  5. [SOLVED] Conditional Format for blank cell to activate error message in following cell
    By tittbb in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-28-2014, 04:07 AM
  6. Replies: 1
    Last Post: 04-11-2013, 01:49 PM
  7. Conditional format of cell to show value of text cell?
    By JapanDave in forum Excel General
    Replies: 2
    Last Post: 05-19-2010, 11:11 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1