How To Highlight Row If Cell Contains Text/Value/Blank In Excel

Question

Hi 

Referring to column H, I have created a number of rules to recognise a word in column H (e.g. nothing), and then colour the whole row (e.g. blue) if that word is present (see below).  This has worked well for six different terms (e.g. "Nothing", "Live", "Keep an Eye On").  See below:

However, no matter how hard I try, I cannot get the same formula to work for a seventh term, "closed profit".  I am trying to have the row coloured green when "closed profit" is present, but it always ends up white as above. Here is a screenshot of the relevant rules:

As you can see from above, the formula is exactly the same for all seven terms, but it will not recognise "closed profit" and colour the row green.

Any help will be much appreciated.

Thanks.

Michael

However,

Answer 1

Try setting the "Applies to" range for all of the rules to the entire range you want to format, e.g. to $H$2:$Z$1000, or if you want to format entire rows, $2:$1000 for example. It is important that the range starts with the same row that you use in your formula.

Answer 2

Thanks Hans.  I'll give that a go.  Actually, I just removed "closed" and put "loss" and "profit" instead of "closed loss" and "closed profit" and it works fine.  Seems like there was a problem differentiating between two terms that began with the same word, or perhaps I was doing something else wrong.  In any case, thanks for your reply.

Answer 3

Hi Michael,

It works from my side when using Hans' suggestion, let us know if it works on your end.

Regards,

Joanne

Answer 4

Hi Joanne

Yes, have it sorted now. Thanks for the message.

Michael