Conditional Formatting Based on Multiple Text Values

Hi everyone,

I'm new to the forum and have been searching around for a way to color cells based on a range of text. For example, I would like to enter a number of cities in a column and based on the region they are in the cell color would change. So, if I entered Chicago, Boston or New York those cells would be blue. If I entered Los Angeles, California or San Jose, the cells would turn red. I don't want to enter another column for region and I don't know macros and all that. I do know a little about the rules within the conditional formatting. I figured out how to make a cell color change for one city but I don't to make 50 rules when I could do 4. Is there a way to make a formula within the rule to capture for a range of cities that would change to one color?

Please be specific as I am not a pro when it comes to the formulas.

Thanks in advance

The only way I can see this working is if you have a separate table with the cities and whichever region they are in. For example, your table might look like this

City Region
LA 1
San Jose 1

etc.

And then you can use a lookup value to determine the formatting

Edit: Actually I lied. Assuming the city is in column A, starting at A2, you could have a rule using a formula like =OR(A2="Los Angeles",A2="San Jose"... etc)

OK if I use the Conditional formatting in the ribbon the formula doesn't work. I can apply to an empty cell referencing the other cells but I get a true/false return, not a color change.

Hi Butman,

Can you upload an example of your workbook?

Here it is cffndncr. Notice that I went in and made rules for each city since I can't figure it out. I tried to make a list on the second sheet of the cities that fall into a region (not up-to-date at the moment). Think of it as a radius. Cities that are closest are green (very close), then blue (a little travel but OK), then orange (not desired but doable), then red (not possible). Feel free to dump the rules in favour of four. As the list grows it's likely that more cities will need to be added and require more rules - rather tedious.

Thanks

Sorry, forgot to upload and I can't do it in edit mode...

Hope this works

Electrical Companies.xlsx

Hi Fotis1991,

Sorry, somehow my edited post came in after your input. I hadn't seen it yet. Thanks for uploading a sample file. I will check it out and get back to you - much appreciated!

Hi Butman,

Sorry, I have been MIA over the weekend!

Daddylonglegs pretty much outlined what I was suggesting. I have attached a sample of how this would work. You add the city and the region to the 'region' tab, and the conditional formatting on the main sheet will adjust accordingly.

Hi everyone,

Thanks for your input.

Fotis1991: thanks for uploading an excel file. I used your formulas, modified them and everything worked great. What I would like now is a way to simply update the Region tab (no. 2) and have it apply to tab 1 so I don't have to go in and modify formulas.

Daddylonglegs / Cffndncr: I liked your suggestions but I couldn't modify the Region tab in the excel Cffndncr uploaded. Obviously, I'm doing something wrong. I have uploaded version 1 thanks to Fotis1991. version 2 is the one I need help with. Also, is there a way to do away with the region number in B column of Region tab? If you look at version 1, you will see how I would prefer to have regions laid out. I've heard of naming a group of cells. Maybe that's the key to this all.

Electrical Companies V1.xlsxElectrical Companies V2.xlsx

Hi Butman,

I'm not sure why you couldn't change the region tab. Did you enable editing?

Man, I just looked at it again and now I get it. I changed the rules slightly when I shouldn't have. I simply added cities in tab 2 and it works. I didn't really want the extra region column in there but at least I know how it works (although I don't fully understand what's going on in the formulas.


OK, now I'm trying to do the same thing in tab 1, column 2. I wanted text that was NOT "ON" to be red but it's not working (it's the last rule in the CF manager.