Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formatting question | Excel Discussion (Misc queries) | |||
Help using Conditional Formating of Entire Rows | Excel Worksheet Functions | |||
Excel Macro Question about Conditional Formatting | New Users to Excel | |||
more than 3 conditional formating in excel | Excel Discussion (Misc queries) | |||
Conditional Formating when result is text | Excel Worksheet Functions |