Any ideas, please?

The sub below stops with Run-time error '6':
Overflow

Sub ColourIt()

NumOfRows = Cells(Rows.Count, 1).End(xlUp).Row

Dim OnHand As Integer
Dim ATS As Integer

For CurrentRow = 2 To NumOfRows

OnHand = ActiveSheet.Cells(CurrentRow, 6)
ATS = ActiveSheet.Cells(CurrentRow, 7)

If OnHand + ATS1 = 0 Then
Cells(CurrentRow, 2).Interior.ColorIndex = 22
End If

Next
End Sub
--
Thanks & Best Regards

Next time it stops, go into debug mode and hover over *CurrentRow* in
the code. It should tell you its value. Then go to your sheet and to
that row and look in column F. Is the number there greater than 32,767
or less than -32,768? I bet you it is.
Change:
Dim OnHand As Integer
to:
Dim OnHand As Long

It might do to do that for ATS too.
Note that later in the code there's a variable called ATS1 which is
always empty; a typo?


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=133687

Thanks for the quick response! Long solves it!

OnHand + ATS is 32,767 and The ATS1 is a typo

After your response found Data Type Summary

Would double be even less likly to error?

Integer 2 bytes -32,768 to 32,767
Long 4 bytes -2,147,483,648 to 2,147,483,647
Double 8 bytes -1.79769313486231E308 to -4.94065645841247E-324 for negative
values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values

--
Thanks & Best Regards


"p45cal" wrote:


Next time it stops, go into debug mode and hover over *CurrentRow* in
the code. It should tell you its value. Then go to your sheet and to
that row and look in column F. Is the number there greater than 32,767
or less than -32,768? I bet you it is.
Change:
Dim OnHand As Integer
to:
Dim OnHand As Long

It might do to do that for ATS too.
Note that later in the code there's a variable called ATS1 which is
always empty; a typo?


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=133687

Would double be even less likly to error?

Likeliness depends -entirely -on the likeliness of your data to exceed
-2,147,483,648 to 2,147,483,647 (and not being a whole number!).


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=133687

Instead of Integer use LONG

"LeeL" wrote:

Any ideas, please?

The sub below stops with Run-time error '6':
Overflow

Sub ColourIt()

NumOfRows = Cells(Rows.Count, 1).End(xlUp).Row

Dim OnHand As Integer
Dim ATS As Integer

For CurrentRow = 2 To NumOfRows

OnHand = ActiveSheet.Cells(CurrentRow, 6)
ATS = ActiveSheet.Cells(CurrentRow, 7)

If OnHand + ATS1 = 0 Then
Cells(CurrentRow, 2).Interior.ColorIndex = 22
End If

Next
End Sub
--
Thanks & Best Regards