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]

Also, sometimes it is saying like below, "with warnings or remainders" but i only want to highlight if it has warning messages, i dont need to highlight if there's no warning (remainder messages should be ignored and not be flagged to be highlighted.) So there is a variable number in between the words "was/were" and "warning message"

See Sample status below:


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

Highlight:
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]

How to formulate

Hi Guys! sorry i think i wasn't clear on my initial question.

If i have below sentence and i want to highlight the whole row (my range is from A2 to G) whenever it detects the phrase "there was 1 warning message" on column G. However, my problem is sometimes there is more than 1 warning message. So "1" is really a variable and is always changing.. how can i set up the conditional formatting rules for this? Any ideas on how to proceed?

=COUNTIF($A2:$H2,"*there was 1 warning message*")>0

Script completed with warnings or remainders. There was 1 warning message. There were no remainder messages.

 

[AlanY]

Re: How to formulate

try

=COUNTIF($A2:$H2,"*there was*"&A1&"*warning message*")>0

with the variable in A1

 

[Fluff]

Re: How to formulate

@LeanneBG
Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. (rule 12 here: Forum Rules).

I have merged both threads

 

[LeanneBG]

Re: How to formulate

Sorry for this Fluff. And thank you for informing me.. wont happen again

 

[LeanneBG]

Re: How to formulate

Hi AlanY, should i put the variable in cell a1? What if i'm not sure what the next number will be..? The number is really random and will depend on how many errors/warnings the process will have

 

[AlanY]

Re: How to formulate

Hi AlanY, should i put the variable in cell a1? What if i'm not sure what the next number will be..? The number is really random and will depend on how many errors/warnings the process will have

this will yield true for any number of error messages

=COUNTIF($A2:$H2,"*there was*warning message*")>0

is that what you wanted?

 

[LeanneBG]

Re: How to formulate

Yes, the variable will always be numbers, and will not really exceed 100. But sometimes it yields a "there was no warning message". I dont want to highlight if it will say "no" in between there was and warning message.

 

[AlanY]

Re: How to formulate

let says

A2 and B2 both has "there was 1 warning message"
and H2 has "there was no warning message"

what to you want the CF to do?