Forum Discussion
highlighting a row based on multiple piece of text (eg in a calendar of people's availability)
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.
- =AND(MATCH("*xx*",$C2:$F2,0),MATCH("*family holiday*",$G2,0))
Here are the conditional formatting formulas:
1:
=MATCH("*xxx*",A$2,0)2.
=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:
=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?
- I didn't know family holiday was a partial match. You could use MATCH with the wildcard. Same idea as the "*xx*".
