Apply Formatting if Any of These Words are Present

Hi. My issue seems simple enough, but not for me, so thanksalot for reading.

Column A has 200 names.

I want the names John, Tom, George, Frank and BillyBob to be filled with blue.

I want the names Sally, Mary, Kathy and Gertrude to be filled with pink.

I want the names Ebenezer, Vader and Fagin to be filled with black...and so on.



Thanks in advance. JS

Select all the cells, starting from A1, then click on Conditional Formatting, New Rule, then choose "Use a formula ..." from the bottom of the list. Put this formula in the formula box:

=ISNUMBER(MATCH(A1,{"John","Tom","George","Frank","BillyBob"},0))

then click the Format button, Fill tab and choose blue. OK your way out.

Then repeat for the other conditions, changing the names in the formula to suit and choosing the appropriate colour.

Hope this helps.

Pete

Pete, thanks for your help. I created a new workbook to test the fix suggested. I received the following error message:

"You may not use reference operators (such as unions, intersections, and ranges)or array constraints for Conditional Formatting criteria."

Sounds like a train wreck to me, but I don't know anything about trains or excel. I doubled-checked by copying and pasting the formula exactly with no luck.

Any suggestions? Thanks again, Pete!

Glad to be able to help. Setting up named ranges for your names would be more flexible if you wanted to change any of the names in future. Set up named ranges called "names_1", "names_2" etc and then your CF formulae would be:

=ISNUMBER(MATCH(A1,names_1,0)) ---> blue

=ISNUMBER(MATCH(A1,names_2,0)) ---> pink

and so on.

Hope this helps.

Pete