L
Luke
Hi
I’m 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) don’t work.
Am a bit of a novice with VBA so any help would be much appreciated.
Many thanks
Luke
I’m 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) don’t work.
Am a bit of a novice with VBA so any help would be much appreciated.
Many thanks
Luke