Conditional formatting list of cells of text

[skinnyboy77]

I have cells in the range of B1:V70, some with text, some blank. I want to color-code the names in those cells, based on the name alone. The project started small, so I had each person with a rule set for them:
Cell value equal to ="John Smith" and applied it to =$B$1:$V$70 and format set to red text. As the project has grown, I now have 12 people that need to be red, 38 blue, 25 green, etc., yet I still have one rule for each person.

I want to eliminate as many of those rules as possible, so the plan was to eliminate them and replace them with something like:
Formula: =OR("John Smith","Mary Jackson","Bob Miller") apply it to =$B$1:$V$70 and format set to red text.

However, that didn't work - all the names show up in black text. What do I need to change to get this to work?

[DBY]

Hello

If you want to hard code your names in, try the following. Select your range B1:V70 and enter the following conditional formatting formula and select your red text format.

=OR(B1="John Smith",B1="Mary Jackson",B1="Bob Miller")

But as you have up to 38 names for a particular format, it migh be better to create lists of names and use something like the following. The first list of names for the 12 reds in this example is in K1:K12. Then apply the following conditional formula accross B1:V70.

=COUNTIF($K$1:$K$12,B1)

You can repeat this for other lists and apply different formatting.

Hope this helps.

[skinnyboy77]

Both methods worked like a charm, but I'm employing your second suggestion. Now, I've applied the new formula to Sheet2 and deleted (very, very slowly - one at a time) all the old individual conditions. Is there a simple way apply the new rules for Sheet2 (and not any of the old rules) to Sheet3, Sheet4, etc.? All the other sheets were using the old set of conditions.

[DBY]

Hello
Glad it worked. Personally I would clear the old rules first then add my news ones. I'm not sure which version of Excel you're using but to delete all conditional formatting in Excel 2003, select the cells to be cleared then on the menu go to Format > Conditional Formatting > Delete > Select Conditions 1, 2 & 3 > Ok > Ok again. You'll then have to apply your new conditions.

In 2007 select the cells to be cleared then go to Conditional Formatting on the Home Ribbon > Clear Rules > Clear Rules from selection. Then apply your new rules.

These are the quickest ways I can think of at the moment.

Regards DBY