Overflow error - same code, different sheet

[rwnelson]

When I put the following code into my first sheet, it runs fine but
when I put the same code into a second sheet, I get a runtime overflow
error as noted in the code below. The only change in the code is that
on the first sheet, it is set to run on Worksheet_Change and on the
second sheet, it is set to run on Worksheet_Activate. I'm not sure why
I'm getting the error. I have 12 sheets in my workbook that the code
needs to go in to and I don't know how to loop the code for all 12
sheets so I'm trying to put them in on each sheet. Thanks in advance
for any help.


Sub Colors()

Dim iRow As Long
Dim iCol As Long

For iRow = range("F7").Row To range("R42").Row Step 7
For iCol = range("F7").Column To range("R42").Column Step 2
doRange Cells(iRow, iCol)
Next iCol
Next iRow

End Sub

Sub doRange(rngtopleft As range)

Application.ScreenUpdating = False

With rngtopleft.Resize(7, 2)
If rngtopleft.Value < Date Then
.Interior.Pattern = xlGray50
Else
.Interior.Pattern = xlSolid
End If
'End With

'TEST CODE
'If range("A1").Value = range("M2") Then
'Exit Sub
'Else
'With rngtopleft.Resize(7, 2)
'range("A1") = range("M2")

If rngtopleft.Interior.ColorIndex = 15 And rngtopleft.Value >= 1 Then
If rngtopleft.Column = range("F5").Column Or _
rngtopleft.Column = range("R5").Column Then
.Interior.ColorIndex = 37
Else
.Interior.ColorIndex = 40
End If
End If

If rngtopleft.Value = "" Then
.Interior.ColorIndex = 15
End If
End With
'=========================================================
'****OVERFLOW ERROR HERE*****
With rngtopleft.Resize(1, 2)
If rngtopleft.Value = DateSerial(range("M2"), 1, 14) + TimeSerial(20,
0, 0) Or _
rngtopleft.Value = DateSerial(range("M2"), 1, 21) + TimeSerial(20,
0, 0) Then
'=========================================================

If rngtopleft.Value = DateSerial(range("M2"), 1, 14) +
TimeSerial(20, 0, 0) Then
.Interior.ColorIndex = 5
.Font.ColorIndex = 2
With rngtopleft
.Offset(0, 1) = "Derrick"
End With
End If

If rngtopleft.Value = DateSerial(range("M2"), 1, 21) +
TimeSerial(20, 0, 0) Then
.Interior.ColorIndex = 5
.Font.ColorIndex = 2
With rngtopleft
.Offset(0, 1) = "Jonathan"
End With
End If

Else
If rngtopleft.Column = range("F5").Column Or _
rngtopleft.Column = range("R5").Column Then
.Interior.ColorIndex = 37
With rngtopleft
.Offset(0, 1) = ""
End With

Else
.Interior.ColorIndex = 40
With rngtopleft
.Offset(0, 1) = ""
End With
End If
.Font.ColorIndex = 1
End If


If rngtopleft = "" Then
.Interior.ColorIndex = 15
End If
End With

'End If
End Sub

 

[tony h]

what is the value in M2 when you get the error?

rgard

 

[rwnelson]

M2 is the year, currently 2007, and the code on sheet 1 will run when
this is changed.

 

[rwnelson]

I think I should also note that on every subsequent sheet, M2 = P3
which is =DATE(Jan!M2,1+1,1)+TIME(20,0,0) and the month is edited
appropriately for each sheet.

 

[rwnelson]

On the first sheet, M2 is the year, currently 2007. On every
subsequent sheet, M2 = P3 (format "yyyy") which is
=DATE(Jan!M2,1+1,1)+TIME(20,0,0) and the month is edited appropriately
for each sheet.

 

[rwnelson]

I found that I get the overflow error because of the M2 = P3. When I
changed this and manually put in the date it works fine. Is there a
way to get around this?

 

[rwnelson]

I figured it out. I put in the first line of the code as range("m2") =
sheets(1).range("M2") and this solved the formula problem.