Conditional Formating Extreme Question

[Guest]

Range of data is in cells Z4Z1000 (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(Z4Z1000>0) blue; =OR(Z4Z1000="END") grey;
=OR(Z4Z1000="NONE") dark grey; =OR(Z4Z1000="#VALUE") light yellow;
=OR(Z4Z1000=0) light blue; =OR(Z4Z1000) 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,

 

[Bob Phillips]

Get the order right.

Condition 1. Test for End

Condition 2. >0

Condition 3. =ISERROR(A1)

and look at http://www.xldynamic.com/source/xld.CFPlus.Download.html

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

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("Z10Z1000").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

 

[Guest]

You might also have to play some with the conditional staelements. i assumed
most of the cells would have a number greater than 0. this macro can take a
while.

 

[Guest]

Thanks to both of you.

I will try both ideas. I'm still learning what order to put things so Excel
understands and I get the answers I want.

Again, thank you.

 

[Guest]

OK - it's been a week or so - but I've been working on this diligently!
I downloaded the add-in conditional formating for excel. Works great! I'm
having just a few remaining problems - if anyone can help??

I have 6 CF statements:
1) Z4=iserror(Z4) yellow
2) Z4="$0.00" light blue
(I changed this to text b/c I was having problems mixing text and
numbers!)
3) Z4="END" dark blue
4) Z4="NONE" grey
5) Z4="DEMO" orange
6) Z4>0 blue

The Greater than 0 is where I am having difficulties. My cell range is
Z4:AL99. CF works perfectly until Row 51, then it stops highlighting cells
that are Greater than 0. All other conditions continue to work. Any ideas
out there???

I've been reading -- Biff mentioned to someone about MOD, could that be the
problem? I have only a few days left before my presentation or else, this
report is going without CF'ing!

Thanks for any help out there,
Heather