fomatting cells based on difference between values of cells two ranges.

**KATIEexcel** · 06-05-2013, 03:53 AM

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

**ragulduy** · 06-05-2013, 04:08 AM

for each cell in Range(Cells(a,7),cells(r,7))
If Cells(cell.Row, 40) - cell.Value < 10 And Cells(cell.Row, 40) - cell.Value >= -10 Then cell.Interior.Color = RGB(255, 255, 255)
next cell

I suppose. I don't think coding it how you were thinking with the calculation written as a worksheet formula would be any quicker.

Edit: I think you would need quite a lot of rows to be concerned about this running slowly - have you turned off screen updating? That might help a bit. (put application.screenupdating = false before and application.screenupdating = true before and after the code that is changing the worksheet.)