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

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

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

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

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