locked
Macro Error 1004 RRS feed

  • Question

  • Hi, I am new in Macro. Need helps to solve the error 1004 issue. There are no errors during compile but when I am running the Macro i prompt me the error 1004. The line with bold font is where the error start.

    Code as below:

    ColorMe
    Sub ColorMeNow()
    On Error GoTo colorerrorhandler
    wrkSheet As Worksheet
    Dim Row, Col, DataCol, MaxRow, Invt, Moq As Integer
    Set wrkSheet = ActiveWorkbook.ActiveSheet
    If Cells(2, 256).Value = 2088 Then
    Row = Cells(2, 254).Value
    Col = Cells(2, 255).Value
    DataCol = Cells(2, 253).Value
    MaxRow = Cells(2, 252).Value
    Invt=Cells(2,250).Value
    Moq=Cells(2,251).Value

     Do While Row < MaxRow
    If (Cells(Row, Col).Value > 0 And Cells(Row, Col).Value < 50) Then
         
              For Offset = 1 To DataCol
                 If Now() - Cells(Row, Col - Offset).Value > ((Cells(Row, Col).Value * 30) + 30) Then
                    If IsDate(wrkSheet.Cells(Row, Col - Offset).Value) = True Then
                    Cells(Row, Col - Offset).Interior.ColorIndex = 3
                    End If
                 ElseIf Now() - Cells(Row, Col - Offset).Value > (Cells(Row, Col).Value * 30) Then
                    Cells(Row, Col - Offset).Interior.ColorIndex = 38
                 Else
                    Cells(Row, Col - Offset).Interior.ColorIndex = 4
                 End If
              Next

    If Cells(Row,Invt).Value > Cells(Row,Moq).Value then
    Cells(Row,Invt).Interior.ColorIndex=3
    else
    Cells(Row,Invt).Interior.ColorIndex=4
    End if
         
           End If
           Row = Row + 1
         Loop
     End If
     Exit Sub
     colorerrorhandler:
     MsgBox "Error while running Macro."
     End Sub
    End Sub

    Thanks

    Thursday, June 13, 2013 4:23 PM

All replies

  • Add this just before that line and report back with what the message says:

    MsgBox _
    "   Row: " & Row & Chr(10) & _
    "  Invt: " & Invt & Chr(10) & _
    "   Moq: " & Moq & Chr(10) & _
    " First: " & Cells(Row, Invt).Value & Chr(10) & _
    "Second: " & Cells(Row, Moq).Value

    Thursday, June 13, 2013 5:01 PM