3

I want the cell to be formatted if the cell contains any of the following words: blue, orange, red, green, and white. I also want to not make 5 different commands for each (this is because I have a loooong list in my actual file).

The entry blue|orange|red|green|white does not seem to work; nor does the entry blue OR orange OR red OR green OR white. How do I do this?

7

IMO better without "Text contains".

To avoid the possibility of conflicting rules, clear all conditional formatting from ColumnA (assumed because OP does not say where the data is), select it and apply a Custom formula is of:

=regexmatch(A1,"blue|green|orange|red|white")

Select your formatting and Done.

This will format blue-green as well as blue and Red car as well as red, but also coloured.

If to format only cells that contain the selected colour names (and nothing else, not for example White paper), try:

=match(A1,{"blue","green","orange","red","white"},0) 

For your locale you might need ; rather than ,.

REGEXMATCH.

MATCH.

  • thanks, but what the "A1" stand for? (=RegExMatch(A2;"xx") applies the formating to the cell above, why?) – JinSnow Mar 1 at 6:18
0

Use a formula for conditional formatting, like

 =MATCH(<cell>, {"blue", "orange", "red", "green", "white"}, 0)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.