change cell background colour after subtracting

Are you making the calculation in column C?

Spreadsheet formula plus Conditional Formatting might get the results you want.
Where do you want the differences?

If you go to conditional formatting you will end up with a table like this

<TABLE style="WIDTH: 126pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=168 border=0><COLGROUP><COL style="WIDTH: 42pt" span=3 width=56><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 42pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=56 height=17>A</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 42pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=56>B</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 42pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=56>C</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>12</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">3</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BACKGROUND: #00b050; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; mso-pattern: black none">9</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>13</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">15</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BACKGROUND: yellow; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; mso-pattern: black none">-2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>15</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">18</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BACKGROUND: red; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; mso-pattern: black none">-3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>17</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">17</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BACKGROUND: #00b050; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; mso-pattern: black none">0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>23</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">21</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BACKGROUND: #00b050; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; mso-pattern: black none">2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>23</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">24</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">-1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>24</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">26</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BACKGROUND: yellow; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; mso-pattern: black none">-2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>2</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">2</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BACKGROUND: #00b050; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; mso-pattern: black none">0</TD></TR></TBODY></TABLE>

Sorry, I would like the change to be shown in column A.

You can still use a formula inside the conditional formatting to compare the two cells. Just apply it to column A

How would I do that, I am using Excel 2003? Many thanks

sadsfan said:

How would I do that, I am using Excel 2003? Many thanks

Click to expand...

Hmm.. I don't remember 2003 that well. When you go in to create a conditional formatting rule, do you have the option to use a formula to determine which cells to format?

If so, then you can use a formula like =a1-b1>0 and apply the green formatting. then create another rule for =a1-b1=-2 and apply the yellow. and finally = a1-b1<-2 and apply red.

You would then make each of these rules apply to range a1:a45. Just make sure that your formatting formulas are relative and don't have the $ in them (which i think it does by default).

If you don't have that option, then I am sorry, i don't remember how to do it in 2003.

MikeLiberty said:

Hmm.. I don't remember 2003 that well. When you go in to create a conditional formatting rule, do you have the option to use a formula to determine which cells to format?

If so, then you can use a formula like =a1-b1>0 and apply the green formatting. then create another rule for =a1-b1=-2 and apply the yellow. and finally = a1-b1<-2 and apply red.

You would then make each of these rules apply to range a1:a45. Just make sure that your formatting formulas are relative and don't have the $ in them (which i think it does by default).

If you don't have that option, then I am sorry, i don't remember how to do it in 2003.

Click to expand...

Thanks Mike, no 2003 doesn't, is it possible to write it in vba code?

sadsfan said:

Thanks Mike, no 2003 doesn't, is it possible to write it in vba code?

Click to expand...

You definitely could but it would take some work. Someone else might have code available.

I'm pretty sure you can use formulas in 2003.

http://chandoo.org/wp/2009/03/13/excel-conditional-formatting-basics/

http://www.contextures.com/xlcondformat03.html