SOLVED

New Contributor

# 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.

6 Replies

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

Here are the conditional formatting formulas:

1:

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

2.

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

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

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?

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

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

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

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)
Solution

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

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

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

@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*