shade cells based on conditions - i have more than 3 conditions

[Guest]

example:
IF A2 = "1",
shade cell E4 "green"

if A2 = 2, shade e4 blue
if 3, shade yellow
if 4, shade brown
if 5, shade orange
if 6, shade purple
if 7, shade black

Conditional formatting will only cover 3 conditions..so u see my problem..
so How would i do this?
is it possible to shade a cell as a result of an IF statement yielding
true/false?
(i'm looking for a way without resorting to vb code, as i dont know much
about it... but if there is no other way, i'm willing to learn)

thanks in advance for your help

 

[T. Valko]

There is an add-in that will extend the conditions to 30 (I think).

http://xldynamic.com/source/xld.CFPlus.Download.html

Otherwise, your only other option is VBA code.

Biff

 

[Guest]

Thanks alot. it works perfectly

2 questions though..

1) i was planning to save my file as a template and send it to other people.
Would they need this macro installed, to have this conditional format
retained (that i have set) ?

2)
how would i resolve this original problem through code?
i'd assume it to be simple enough to have certain formula results trigger a
simple change of cell background color.
can you pls help with a script, and guide me on how to load it?
i'd very much appreciate that. it would be a good learning experience
but if not, thanks anyway. you've been a big help

 

[T. Valko]

1. Yes, that add-in would have to be available to those you send the file
to.

2. I don't know enough VBA to be able to help you with that. You can wait
and see if anybody else replies or you can post your question in the
programming group. Or you could do a search of "conditional format more than
3 conditions". I'm sure you'll get lots of hits. This is a frequently asked
question here.

Biff