Conditional formatting using multiple different text

I use MS Excel 2007 and I am having trouble creating a database. I need to have certain cells formatted to turn certain colors depending on what name is in them.

E.g If cells A1:AJ18 = "Bob Smith" that cell needs to turn red. If cells A1:AJ18 = "Chad Smith" that cell need to be blue, etc.

I have tried different conditional formatting/rule making scenarios and nothing seems to be working.

Any help would be greatly appreciated!

Thanks for the info!

unfortunately, this did not work. I think I have some other things going on in my chart that may be preventing this from working.

I have a lot of cells merged together, creating a bigger space for my names to appear in. So the names I need highlighted, aren't in one cell but essentially in two cells. I am not sure if it's possible to do what I want to do with this being the case.

Also, it's a situation where I need to enter a name, and then the cell turns either red, blue, green etc. based on if that persons name matches a list of about 25-75 names. So if the name I enter is Bob Smith, the cell might turn red. If the name I enter is Joe Smith, the cell might turn green. So would I need to enter in every single possibility into the CF formula bar or can I list them in a different part of the workbook and just enter in the cell range that that encompasses the names?

you can make as many cf rules as you want, or you could have cf rules for various ranges. both ways will work. if you post a sample .xls, we can take a look for you.

Ok thanks!

I think I need to look at how many cells I am merging and see if I can make it more simplistic.

Is it possible to simply create a rule for cells that contain specific text, and enter in more than one name as the specific text? E.g. In the specific text window I can enter "Bob Smith", "J. Smith", etc?

Yes...
However, you're doing a different color for each name.
So you need a seperate conditional format formula for each different color you're using.

Thanks!

Is there a specific way I need to write the multiple names in the window? I have tried a few different ways and it doesn't appear to be working. Only cells that contain - specific text - containing - "B. Smith", "J. Smith" etc. This didn't seem to work. I have a big selection of cells highlighed for this rule, C3:AE36 with merged cells as well. Not sure if thats the issue.

You're not really listening...

You have to create 1 rule for each individual name..
Because you're changing the cells to a different color for each name.

If you were changing ALL specified names to the same color, then yes it would be doable in one formula.
But since you want
"Bob Smith" red and "Chad Smith" blue, etc...
Then you can't do it in a single formula.

1 Conditional format formula for Bob Smith
and another for Chad Smith

Yes, but I want multiple names to be blue. B. Smith, C. Smith, D. Smith all to be blue. And then T. Doe, B. Doe to be red. So I need multiple formulas per color, I get that.

What I am saying is that, I must not be writing the formula correctly because it's not working. Also, I am not sure if my multiple merged cells are the problem.

Oh, Ok..

Setup some lists on your sheet to hold the names..
Say for example Z2:Z4 = B.Smith C. Smith and D.Smith

Then in conditional formatting, choose "Use a formula to determine...."
And use
=MATCH(A1,$Z$2:$Z$4,0)
Adjust A1 to the currently active cell at the time you go into conditional formatting.

OK thanks.

Just to make sure I am understanding this correct, A1 would be the cell I want to be highlights right? Based on which name is in it, with reference to the cells Z2:Z4.

What if I needed the highlighted cell to be in a range? For instance, in my chart....which is say A1:AE36, I have a name in each cell, or will type a name in each cell. I need to create a rule, for that entire range that if the name is one of the names in Z2:Z4, that cell in my A1:AE36 range turns red. Is this possible?

Yes, if your whole range is A1:AE36..

Put the list somewhere OUTSIDE that range..say A100:A102

Then highlight your whole range A1:AE36
Go to conditional formatting and use formula
=MATCH(A1,$A$100:$A$102,0)

Yah, unfortunately the merged cells are a necessity.

Thanks for all your help,I think I have it now!

Thanks!!

hELLO

I'm trying conditional formatting two text type of text: "Low" in Yellow and "very Low" in Green. Unfortunately, Excel is picking both either yellow or green. Everything!