+ Reply to Thread
Results 1 to 7 of 7

Overflow error - same code, different sheet

  1. #1
    rwnelson
    Guest

    Overflow error - same code, different sheet

    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


  2. #2
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,186
    what is the value in M2 when you get the error?

    rgards

  3. #3
    rwnelson
    Guest

    Re: Overflow error - same code, different sheet

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


  4. #4
    rwnelson
    Guest

    Re: Overflow error - same code, different sheet

    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.


  5. #5
    rwnelson
    Guest

    Re: Overflow error - same code, different sheet

    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.


  6. #6
    rwnelson
    Guest

    Re: Overflow error - same code, different sheet

    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?


  7. #7
    rwnelson
    Guest

    Re: Overflow error - same code, different sheet

    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.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1