highlighting a row based on multiple piece of text (eg in a calendar of people's availability)

[Jason2235]

Hi All,

An absolute laymen here - I've been trialling and erring trying to sort out a home made calendar of people's availability and it doesn't do what I would like - I have figured out some elements (thanks to online searches).

Basically I've figured out how to Bold and highlight a row in grey if "Sunday" exists: 

=SEARCH("Sunday", $A2)>0

apply to: $A$2:$G$33

and another formula for Saturday which also bolds and highlights the row in grey:

=SEARCH("Saturday", $A2)>0

apply to: $A$2:$G$33

2 QUESTIONS

1)

Where "xxx" exists in any cell from C2 through F2 then highlight in red the whole row (eg. A2 through F2)

I figured it out for one cell:

=SEARCH("xxx", ($C2))>0 

Apply to: $A$2:$G$33

I've received other outputs such as:

- if column C contains "xxx" or "XXX" then just that cell highlights.

- I somehow managed to highlight the entire column C (eg. C2 though C33)

- next I tried amending based on some info I saw online and then a single cell two columns to the left of the cell containing the "xxx" was highlighted.

Once I figured out the formula further above I ended up just creating the same formula 4 times - one for each cell:

I've only been able to figure the rule out per cell - to highlight the row (eg. 4 formulas):

=SEARCH("xxx", ($C2))>0 

Apply to: $A$2:$G$33

=SEARCH("xxx", ($D2))>0 

Apply to: $A$2:$G$33

=SEARCH("xxx", ($E2))>0 

Apply to: $A$2:$G$33

=SEARCH("xxx", ($F2))>0 

Apply to: $A$2:$G$33

How do I make this simpler - one formula to cover those cells?

I've tried something like

=SEARCH("xxx", ($A2:$F2))>0 

Apply to: $A$2:$G$33

But that doesn't do what I would expect.

2)

If "xxx" (or "XXX") exists in C2 though F2 AND "Family holiday" exists in G2 then colour A2 through F2 in yellow. 

Sorry, I know the above is basic compared to a lot of the stuff I see in the forum. Appreciate any assistance.

[#2]

@Jason2235 

Here are the conditional formatting formulas:

1:

=MATCH("*xxx*",A$2,0)

2.

=AND(MATCH("*xxx*",A$2,0),$G$2="family holiday")

[#3]

@Patrick2788 

Thanks for your response - I've made some slight changes and I am just about there!!!! 🙂

I have these two

somebody Unavailable:

=MATCH("*xx*",$C2,0)

(it WORKS!!! - thank you)

Conflict between C2 or D2 or E2 or F2 AND G2 contains a phrase somewhere in the cell then highlight (eg. "xx" AND "family holiday" exist)

=AND(MATCH("*xx*",$C2:$F2,0),$G2="family holiday")

The above works where G2 says exactly "family holiday" - I've tried amending to "*family holiday*" (eg. if the cell says "Family holiday to the coast" then I would still like that to trigger the rule - but when I add the wild card/asterisks the rule doesn't work anymore. Do you know how to tweak it?

[#4]

I didn't know family holiday was a partial match. You could use MATCH with the wildcard. Same idea as the "*xx*".

[#5]

@Patrick2788 

terribly sorry about that - I missed that bit in my original message.

I've tried: 

=AND(MATCH("*xx*",$C2:$F2,0),$G2="*family holiday*")

and it seems to break the formula. If I remove those two wildcards then the formula works again but isn't a partial match which is ideally what I am after. 

[#6 · Accepted solution]

=AND(MATCH("*xx*",$C2:$F2,0),MATCH("*family holiday*",$G2,0))

[#7]

@Patrick2788

you sir should be knighted!!! Thank you, I was messing around yesterday and got quite close to what you have posted above....but not close enough.

Thank you *scrapes and bows*