CODEWITHSUNDEEP
excelexcel-formulaconditional-formatting
Joshua Trimmell
Joshua Trimmell

Reputation: 1

Excel - Conditionally format cell in column if NOT containing specific text and is NOT BLANK

I want the cells in one of my columns to show up bright red when they don't have the word 'BILLED' while also not highlighting all the blank cells. This will allow me to easily find items I need to bill; as I add 'BILLED' to the text after I have billed it.

I tried the obvious, but this highlights all the blank cells. Format only cells with Specific Text not containing 'Billed'

I imagine the solution is to choose 'Use a formula to determine which cells to format' with a formula that first looks for cells with a value and then checks to see if it has the word 'Billed'? Would that be a nested IF statement like 'IF(not Null and if 'BILLED')'?

What is the solution?

Upvotes: 0

Views: 7265

Answers (1)

Jody Highroller
Jody Highroller

Reputation: 1029

The easiest answer is use a formula for your conditional formatting. Assuming you want to format all cells in column G you would use:

=AND($G1<>"BILLED",NOT(ISBLANK($G1)))

Now, if you have blanks that are caused by a formula ISBLANK will not suffice, so you would need to use something more like:

=AND($G1<>"BILLED",SUMPRODUCT(--(LEN($G1)>0)))

CF has a tendency to act a little wonky, so after you enter the formula open the CF dialog back up and make sure it didn't add any extra quotes or anything, and verify your "Applies To" range is still correct.

Upvotes: 1

Related Questions