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
|