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?
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.
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.