Conditional Formatting with multiple text values

[titchestiny]

Hi,

I'm trying to use conditional formatting to colour cells within a column based on the text they contain. For example if A1 = A or B or C or D colour red, if A1 = E or F or G or H colour blue, if A1 = I or J or K or L colour green.

I've tried some formulas, and while they are being excepted, the cells are not changing colour. I can create formatting rules based on a single aspect i.e. if A1 = A colour red, if A1 = B colour red, but I would have to create a lot of rules, which I want to try and avoid. Any help would be greatly appreciated.

Thanks,

Tt

p.s. I'm using Excel 2010

 

[dennisfry]

Ok I am a goofball, I tried it with "Use a formula to determine witch cells to format
with =OR(A1="A", A1="B", A1="C", A1="D") and it worked! Thanks, I will modify it to suit my needs!!!
Thanks, without this thread I would not have got it.
Simple mistake

 

[mukhi]

@DuchDiggy
you are the man! after many searches and tries, i have finally got something that works!
the goal was to format only cells that contain specific texts as opposed to what MS excel offers in the 2007 version: format only cells that contain specific text.
so, for example, if you have a column d having the color names, you use the formula below to find all d column cells having red, orange or purple:

=or(d1="red",d1="orange",d1="purple")

just make sure that you "edit rule" to change "applied to" to =$d$1:$d$100000 (say, if you have 1,00,000 active rows), and check "stop if true".

 

[mukhi]

@DutchDiggy
oh well, this works if the cell has those names ONLY. if, for example, one cell has redpanda or orangeshoe instead of red/orange it doesn't get it.

 

[mukhi]

[FONT=&quot]Looks like I have figured out![/FONT][FONT=&quot][/FONT][FONT=&quot]If the goal is to format only cells that "contain" (not equal to) specific texts, [/FONT][FONT=&quot]for example, if you have a column D having the color names, you use the formula below to find all D column cells having red, orange or purple:[/FONT]

[COLOR=#2A2E2E][FONT=&quot]=OR(ISNUMBER(SEARCH("red",D1)),ISNUMBER(SEARCH("orange",D1)),ISNUMBER(SEARCH("purple",D1)))[/FONT][/COLOR]