Color cells based on text values

I want to format all cells having a distinct value a unique color. These are drop downs cells. For example:

I have cells labeled Recruit, Private, Corporal, Lieutenant, Captain, Major, and Colonel. I want Corporal and Lieutenant to be orange. I want Captain and Major to be Red. I want Colonel to be Green.

I tried conditional formatting using the formula:
=OR($C1="Corporal", $C1="Lieutenant") formatted orange

and created another rule with the formula:
=OR($C1="Captian", $C1="Major") formatted Red

but these would not color the correct cells the correct color.
K TinAnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David EisenbeiszCEOCommented:
You are trying to do too much with a single formula and you are missing a reference cell.  You can apply as many formatting rules as you need to each cell.  Don't use the "OR" function, just use a new rule for each case.

Pull up the conditional formatting window and select "Use a formula to determine which cells to format."  Then click on the box for the formula.  Next you have to pick the cell you want the rule to apply to, just pick one cell for now but when the cell reference appears in the box, push F4 until the $ symbols go away.  You want to apply this formula to any cell, so we don't want hard references.

Next you will take your first rank and input the formula.  It will look like this:  Formula: =C1="Corporal"
Then click on the "Format" button and set the font color you want for Corporal.
Click OK until you get back to the spreadsheet.

Start over again and perform each step exactly the same, but use the next rank and color.  Make sure you pick the same reference cell or it won't work when you are done.  After you have finished making a rule for each rank, make sure your cursor is in the reference cell and pick the format painter.  Use it to paint the formatting to any other cells you want to change to text color by these rules.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
The issue may be the referenced cell in the formula.
If you selected multiple cells for applying conditional formatting, the cell reference used in the conditional formatting formula should be the first cell in the selected cells.
So considering your formula, C1 should be the first cell in the selected cells you want to apply the conditional formatting in.

Refer to the attached in which I have applied the desired conditional formatting based on the formulas you original have.
What I did, I selected C1:C15 and make three rules for conditional formatting.

If you wish, you may select C1 only and make new rules for conditional formatting and then copy the formatting to the other cells down the rows.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.