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.