| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Colour formatting using command buttons and comparing data.
Hi
Im trying to write a bit of code which will do the following using a command button. I have two columns of data I want to compare in the following way; If the difference between Column B and Column A is more than 0% format green, if the difference is between 0% and -5% format orange and if the difference is less than 5% format red. I have come up with the following; Range("B1:B3").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=IF(((B1-A1)/A1)0,True,False)" Selection.FormatConditions(1).Interior.ColorIndex = 4 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "IF(((B1-A1)/A1)<-0.05,True,Flase)" Selection.FormatConditions(2).Interior.ColorIndex = 3 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=IF((AND(((((B1-A1)/A1)<0,(((B1-A1)/A1)<-0.05)),True,False)" Selection.FormatConditions(3).Interior.ColorIndex = 45 Range("A1").Select For the following data A B 1 100 104 2 100 98 3 100 90 Condition 1 seems fine but the other two (Red and Orange) dont work. Am a bit of a novice so any help would be much appreciated. Many thanks Luke |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Colour formatting using command buttons and comparing data.
Why do you need to use a Command Button? Why not just use conditional
formatting and format the cells depending on each of the three criteria? "Luke" wrote: Hi Im trying to write a bit of code which will do the following using a command button. I have two columns of data I want to compare in the following way; If the difference between Column B and Column A is more than 0% format green, if the difference is between 0% and -5% format orange and if the difference is less than 5% format red. I have come up with the following; Range("B1:B3").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=IF(((B1-A1)/A1)0,True,False)" Selection.FormatConditions(1).Interior.ColorIndex = 4 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "IF(((B1-A1)/A1)<-0.05,True,Flase)" Selection.FormatConditions(2).Interior.ColorIndex = 3 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=IF((AND(((((B1-A1)/A1)<0,(((B1-A1)/A1)<-0.05)),True,False)" Selection.FormatConditions(3).Interior.ColorIndex = 45 Range("A1").Select For the following data A B 1 100 104 2 100 98 3 100 90 Condition 1 seems fine but the other two (Red and Orange) dont work. Am a bit of a novice so any help would be much appreciated. Many thanks Luke |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Colour formatting using command buttons and comparing data.
I want to use the colour coding to analysis data so need to be able to easily
turn on and turn off the analysis. The actual spreadsheet is also quite large so when other people use it it would be much quicker for them (and me as i would have to explain to them how to use conditional formatting) to use a command button instead of using normal conditional formatting on individual cells and pasting the formatting. "toni.gee" wrote: Why do you need to use a Command Button? Why not just use conditional formatting and format the cells depending on each of the three criteria? "Luke" wrote: Hi Im trying to write a bit of code which will do the following using a command button. I have two columns of data I want to compare in the following way; If the difference between Column B and Column A is more than 0% format green, if the difference is between 0% and -5% format orange and if the difference is less than 5% format red. I have come up with the following; Range("B1:B3").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=IF(((B1-A1)/A1)0,True,False)" Selection.FormatConditions(1).Interior.ColorIndex = 4 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "IF(((B1-A1)/A1)<-0.05,True,Flase)" Selection.FormatConditions(2).Interior.ColorIndex = 3 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=IF((AND(((((B1-A1)/A1)<0,(((B1-A1)/A1)<-0.05)),True,False)" Selection.FormatConditions(3).Interior.ColorIndex = 45 Range("A1").Select For the following data A B 1 100 104 2 100 98 3 100 90 Condition 1 seems fine but the other two (Red and Orange) dont work. Am a bit of a novice so any help would be much appreciated. Many thanks Luke |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Colour formatting using command buttons and comparing data.
OK, point taken. Here are a couple of issues to consider:
1. Your third condition is slightly incorrect, the second part of the AND statement should be -0.05, not <-0.05. 2. Your formulas are referring to row 1 throughout and that row can only satisy one condition. Try replacing A1 with A:A and B1 with B:B - this instructs the model to consider the values in columns A and B on each row seperately. Also, your formulas contain more pairs of parentheses than are necessary - this doesn't affect their calculation, but perhaps complicates their understanding. Post and let me know if this solves your problem. Regards, "Luke" wrote: I want to use the colour coding to analysis data so need to be able to easily turn on and turn off the analysis. The actual spreadsheet is also quite large so when other people use it it would be much quicker for them (and me as i would have to explain to them how to use conditional formatting) to use a command button instead of using normal conditional formatting on individual cells and pasting the formatting. "toni.gee" wrote: Why do you need to use a Command Button? Why not just use conditional formatting and format the cells depending on each of the three criteria? "Luke" wrote: Hi Im trying to write a bit of code which will do the following using a command button. I have two columns of data I want to compare in the following way; If the difference between Column B and Column A is more than 0% format green, if the difference is between 0% and -5% format orange and if the difference is less than 5% format red. I have come up with the following; Range("B1:B3").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=IF(((B1-A1)/A1)0,True,False)" Selection.FormatConditions(1).Interior.ColorIndex = 4 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "IF(((B1-A1)/A1)<-0.05,True,Flase)" Selection.FormatConditions(2).Interior.ColorIndex = 3 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=IF((AND(((((B1-A1)/A1)<0,(((B1-A1)/A1)<-0.05)),True,False)" Selection.FormatConditions(3).Interior.ColorIndex = 45 Range("A1").Select For the following data A B 1 100 104 2 100 98 3 100 90 Condition 1 seems fine but the other two (Red and Orange) dont work. Am a bit of a novice so any help would be much appreciated. Many thanks Luke |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Colour formatting using command buttons and comparing data.
Great, thanks very much Toni have managed to get it running.
"toni.gee" wrote: OK, point taken. Here are a couple of issues to consider: 1. Your third condition is slightly incorrect, the second part of the AND statement should be -0.05, not <-0.05. 2. Your formulas are referring to row 1 throughout and that row can only satisy one condition. Try replacing A1 with A:A and B1 with B:B - this instructs the model to consider the values in columns A and B on each row seperately. Also, your formulas contain more pairs of parentheses than are necessary - this doesn't affect their calculation, but perhaps complicates their understanding. Post and let me know if this solves your problem. Regards, "Luke" wrote: I want to use the colour coding to analysis data so need to be able to easily turn on and turn off the analysis. The actual spreadsheet is also quite large so when other people use it it would be much quicker for them (and me as i would have to explain to them how to use conditional formatting) to use a command button instead of using normal conditional formatting on individual cells and pasting the formatting. "toni.gee" wrote: Why do you need to use a Command Button? Why not just use conditional formatting and format the cells depending on each of the three criteria? "Luke" wrote: Hi Im trying to write a bit of code which will do the following using a command button. I have two columns of data I want to compare in the following way; If the difference between Column B and Column A is more than 0% format green, if the difference is between 0% and -5% format orange and if the difference is less than 5% format red. I have come up with the following; Range("B1:B3").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=IF(((B1-A1)/A1)0,True,False)" Selection.FormatConditions(1).Interior.ColorIndex = 4 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "IF(((B1-A1)/A1)<-0.05,True,Flase)" Selection.FormatConditions(2).Interior.ColorIndex = 3 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=IF((AND(((((B1-A1)/A1)<0,(((B1-A1)/A1)<-0.05)),True,False)" Selection.FormatConditions(3).Interior.ColorIndex = 45 Range("A1").Select For the following data A B 1 100 104 2 100 98 3 100 90 Condition 1 seems fine but the other two (Red and Orange) dont work. Am a bit of a novice so any help would be much appreciated. Many thanks Luke |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Comparing columnar data to row data | Excel Discussion (Misc queries) | |||
| Comparing 2 pivot tables data using VBA | Excel Worksheet Functions | |||
| Comparing data (Before & After) | Excel Worksheet Functions | |||
| help comparing two sets od data to find the odd data | Excel Worksheet Functions | |||
| Comparing Data in two columns | Excel Worksheet Functions | |||
Linear Mode
