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