Conditional Formatting Rules for variable messages

[LeanneBG]

Hi Experts! I am trying to formulate the best way to create conditional formatting rules to be able to highlight the whole row based on the words found in column G.

Basically, column G contains all the status of the processing of the macro. It is consist of a sentence or two. I want the rule to highlight the whole row when it detects either one of the words/phrases within the sentence like "with warnings" or "With warning" or "with errors" or "with error" or "was terminated". I was able to formulate using format only cells that contains the following words, however, it is not working whenever i am putting the whole sentence. It only works whenever i only put "with warnings" in the cell. Hope you can help. Thanks!

Sample status below:

JOB_ AVCDDE_123456 Script completed with warnings or remainders. There was 1 warning message. There were no remainder messages. Script ran for 52 minutes and 31 seconds. (Elapsed: 3151 Interactive: 0 Processing: 3151)

 

[LeanneBG]

Re: How to formulate

To still highlight the whole row. By the way, the status can only be found on column G and there is only 1 status pero row so the possiblity of having 2 status is unlikely to happen.. so it's only with xx warnings of no warnings at all..hope this info helps..

 

[AlanY]

Re: How to formulate

To still highlight the whole row. By the way, the status can only be found on column G and there is only 1 status pero row so the possiblity of having 2 status is unlikely to happen.. so it's only with xx warnings of no warnings at all..hope this info helps..

in that case, try this

=NOT(COUNTIF(G2,"*no warning message*")>0)

 

[LeanneBG]

Re: How to formulate

Ahh i see, this is actually a better approach. Can i also add "no warning messages"? Just to be sure thaf it will not highlight both "no warning message" or "no warning messages"

Should i just create the same CF rule above and put "no warning messages"

sorry i dont have my laptop with me right now but i will try the solution you provided later as soon as i get back my laptop

 

[AlanY]

Re: How to formulate

Ahh i see, this is actually a better approach. Can i also add "no warning messages"? Just to be sure thaf it will not highlight both "no warning message" or "no warning messages"

Should i just create the same CF rule above and put "no warning messages"

sorry i dont have my laptop with me right now but i will try the solution you provided later as soon as i get back my laptop

the formula in post#12 should work as that exclude "no warning messages"

 

[LeanneBG]

Re: How to formulate

the formula in post#12 should work as that exclude "no warning messages"

Hi @AlanY
I tried below formula, but when i applied this, the whole range was highlighted already since at the start of the process, the range is blank. It will only have data right after the processing is completed

=NOT(COUNTIF(G2,"*no warning message*")>0)

 

[LeanneBG]

Re: How to formulate

Hi @AlanY
I tried below formula, but when i applied this, the whole range was highlighted already since at the start of the process, the range is blank. It will only have data right after the processing is completed

=NOT(COUNTIF(G2,"*no warning message*")>0)

While this =COUNTIF($A2:$H2,"*there was*warning message*")>0 highlights the row even if it says there was no warning message.. i don't want this to happen.. i only want to highlight if it says it has warning message..

 

[AlanY]

Re: How to formulate

have a look if this is what you're after

https://drive.google.com/file/d/1XfyRnyhOOYuHTdOBMduqo0tGBplUH8OJ/view?usp=sharing

 

[LeanneBG]

Re: How to formulate

have a look if this is what you're after

https://drive.google.com/file/d/1XfyRnyhOOYuHTdOBMduqo0tGBplUH8OJ/view?usp=sharing

HI @AlanY
I tried but it is still not working as expected..see below file

https://drive.google.com/file/d/1YtAAPGFNVSk36iPZewzcxv3zi8jDEqgA/view?usp=sharing

 

[AlanY]

Re: How to formulate

modified the CF formula to

=ISNUMBER(MID($G2,FIND("warning message",$G2)-3,2)+0)

that seems to work.

file here:

https://drive.google.com/file/d/15d3wV1jiCk_CK9dOvMtaSHH9B5qSeL9w/view?usp=sharing

 

[LeanneBG]

Re: How to formulate

modified the CF formula to

=ISNUMBER(MID($G2,FIND("warning message",$G2)-3,2)+0)

that seems to work.
file here:

https://drive.google.com/file/d/15d3wV1jiCk_CK9dOvMtaSHH9B5qSeL9w/view?usp=sharing

@AlanY it works! Thanks for all your help. Can you please help translate the formula to me? Just so i can understand what it does..and re-apply in my future macros..