in the conditional formating just be sure what order you put things.

But a brute force macro is not too difficult.
you might have ot play with the color indexes some, my sceen gives some
odd results sometimes.

try

Sub TRY()
Range("Z10:DZ1000").Select
With Selection.Interior
.ColorIndex = 8
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
For r = 10 To 1000
For c = 26 To 130
cel = Cells(r, c)
If cel = "NONE" Then cl = 16: GoTo 10
If cel = "END" Then cl = 15: GoTo 10
If cel = "#VALUE" Then cl = 36: GoTo 10
If cel = "Demo Unit" Then cl = 45: GoTo 10
If cel = 0 Then cl = 37: GoTo 10
GoTo 20
10 Cells(r, c).Select
With Selection.Interior
.ColorIndex = cl
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
20 Next c
Next r
End Sub

"Heather" wrote:

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