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 with VBA so any help would be much appreciated.

Many thanks

Luke

One way:

With Range("B1:B3")
.Interior.ColorIndex = 3 'Default color
With .FormatConditions
.Delete
With .Add(Type:=xlExpression, Formula1:="=B1A1")
.Interior.ColorIndex = 4
End With
With .Add(Type:=xlExpression, Formula1:="=B1/A10.95")
.Interior.ColorIndex = 45
End With
End With
End With


Note that you only need two conditionals, since you're covering the
entire range of numbers (e.g., x<-5%, -5%<=x<0%, x0%) I chose red as
the default color but you could change the formulas to use either of the
others.


In article ,
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 with VBA so any help would be much appreciated.

Many thanks

Luke

Ooops, should have been

(e.g., x<-5%, -5%<=x<=0%, x0%)

In article ,
JE McGimpsey wrote:

Note that you only need two conditionals, since you're covering the
entire range of numbers (e.g., x<-5%, -5%<=x<0%, x0%)

Great, thanks very much.

"JE McGimpsey" wrote:

Ooops, should have been

(e.g., x<-5%, -5%<=x<=0%, x0%)

In article ,
JE McGimpsey wrote:

Note that you only need two conditionals, since you're covering the
entire range of numbers (e.g., x<-5%, -5%<=x<0%, x0%)