Range of data is in cells Z4:DZ1000 (testing smaller range within this range)
each cell has a complicated IF statement where the result of each cell
could be: "None", "Demo Unit", $Amount, "End", $0, or #VALUE (if not enough
info to calculate). Each 6 results represents a valid situation. I would
like to use conditional formating to display an individual color for each
situation.
Example: =OR(Z4:DZ10000) blue; =OR(Z4:DZ1000="END") grey;
=OR(Z4:DZ1000="NONE") dark grey; =OR(Z4:DZ1000="#VALUE") light yellow;
=OR(Z4:DZ1000=0) light blue; =OR(Z4:DZ1000) orange

I realized the example was a great idea but does not work. CF has a
limitation of 3 and I have 6. I looked into writing a macro. Personally,
that is too hard. So, I decided I would just pick 3.

Here are my problems:
1) CF picks the first TRUE statement and formats it. All my statements are
true. They were IF statement to begin with. How do I get around this, or do
I? All my results are scattered throughout the range of cells.

2) CF interprets text (e.g. "END") as being 0. I want text to be text and
numbers to be numbers. What do I do?

3) #VALUE was one of the three that I would like to be CF. How would I
write a formula that Excel would understand what #VALUE is?

As you can tell, I'm not the excel expert. I think when I finish this
spreadsheet...I may be. Any help or ideas will be much appreciated!

Thanks in advance,
--
Heather