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

New Contributor

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




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.



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.

6 Replies



Here are the conditional formatting formulas:





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



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


I have these two


somebody Unavailable:


(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?


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



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. 


best response confirmed by Jason2235 (New Contributor)
=AND(MATCH("*xx*",$C2:$F2,0),MATCH("*family holiday*",$G2,0))


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*