IF statement comparing 2 ranges not giving correct result

[anrichards22]

My code tests one range against another and changes the interior colour
If the two ranges are equal the interior colour should change to orange. If the 2nd is less than the first then the interior colour should change to red.

Please Login or Register  to view this content.

I ran my code outputting the value of rng4, rng5 and the cell address of rng4 and rng5.

27.63 27.63 $J$7 $J$8
2.75 2.76 $K$7 $K$8
12.46 12.46 $L$7 $L$8
$M$7 $M$8
$N$7 $N$8
24.87 24.87 $O$7 $O$8
15.17 15.17 $P$7 $P$8
$Q$7 $Q$8
$R$7 $R$8
$S$7 $S$8
$T$7 $T$8
9.7 9.7 $U$7 $U$8
$V$7 $V$8
$W$7 $W$8
$X$7 $X$8
$Y$7 $Y$8
$Z$7 $Z$8


Based on these numbers J8, L8, O8, P8 and U8 should all be orange.
But only J8 and L8 are orange.
O8 and P8 are red.
U8 is unchanged.

I cannot work out why!
Any ideas on where I might be going wrong?

[xlnitwit]

Hi,

I'd guess you have very small floating point differences between the values. You might round them to say 3 decimal places

Please Login or Register  to view this content.

[anrichards22]

All of the numbers should already be rounded to 2 decimal places but to make sure ...prior to going into an Access table all of the numbers are rounded to 2 decimal places.

Is it possible that in the process of getting from Access to Excel the numbers are changing? As in changing from numbers rounded to 2 decimal place to numbers with more than 2 decimal places?
The numbers are stored in Access as a number with a single field size.

[xlnitwit]

Did you try the rounding? If it works, that will answer your question.

[anrichards22]

I did try rounding the numbers and it did solve the issue.

I would like to know why the numbers are changing during the import process. I will start another thread to find an answer to that question.
Thanks for your help. Much appreciated.