Conditional Formating Extreme Question
 

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

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.....Download.html

--

HTH

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


"Heather" wrote in message
...
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

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

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.

"bj" wrote:

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

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.

---
Heather

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) Z40 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
--
Heather


"Heather" wrote:

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.

---
Heather