I have data in column 7 of a sheet (rows a and r are defined already, don't worry about them). I want to format the colour of each cell according to it's value. Like normal conditional formatting, I suppose.
Initially I was happy with this code:

For Each cell In Range(Cells(a, 7), (Cells(r, 7)))
If cell.Value >= 20 And cell.Value <= 40 Then cell.Interior.Color = RGB(255, 255, 255)
If cell.Value > 40 And cell.Value <= 60 Then cell.Interior.Color = RGB(255, 211, 211)
If cell.Value > 60 And cell.Value <= 80 Then cell.Interior.Color = RGB(255, 121, 121)
If cell.Value > 80 And cell.Value <= 100 Then cell.Interior.Color = RGB(255, 0, 0)
Next cell
Now, however, the range values have changed, and I want to compare each cell in column 7 with a corresponding value, found in column 40.

I don't know how to code it...... i was thinking of something like "if(and( rc40-rc7<10, rc40-rc7=>-10),Then cell.Interior.Color = RGB(255, 255, 255),"")"

Any ideas?

I thought about creating a "calculations" sheet for rc40-rc7, but I don't want to slow down the macro.
I also thought about "For i=a to r, if cells(i,7).value> cells(i,40).value then cell.Interior.Color = RGB(255, 255, 255) Next i" but I worry that will be very slow too.

xx