Han
Han

Reputation: 45

Excel 2013 compare dates on different row using macro "Run time error 13, Type mismatch"

I'm new in vba, currently I'm working and my boss need me to create a excel macro.

The macro function are:

So now I'm stuck until "Run time error 13, Type mismatch".

Below are my codding part, mismatch is between >>> <<<.

Private Sub Worksheet_Activate()
Dim rngA As Range
Dim rngD As Range

With Worksheets("Sheet1")

    For Each rngA In .Range(.Range("A1"), .Cells(.Rows.Count, "A").End(xlUp))
    For Each rngD In .Range(.Range("D1"), .Cells(.Rows.Count, "D").End(xlUp))

        >>> If rngA.Value("A1:xlUp") >= rngD.Value("D1:xlUp") Then <<<
            MsgBox "Sheet 1 Row " & rngA.Row & " expiring"
            rngC.Interior.ColorIndex = 3
            rngC.Font.ColorIndex = 2
            rngC.Font.Bold = True

        End If


    Next rngD
    Next rngA


End With
End Sub

Upvotes: 1

Views: 148

Answers (1)

Karthick Gunasekaran
Karthick Gunasekaran

Reputation: 2713

Please try this

With Worksheets("Sheet2")
lastrow = Range("A1048576").End(xlUp).Row
For i = 2 To lastrow
    If Range("A" & i).Value <> "" And Range("D" & i).Value <> "" Then
        If Range("A" & i).Value >= Range("D" & i).Value Then
            Range("C" & i).Value = "Contract Going to Expire"
            Range("C" & i).Interior.Color = 3
            Range("C" & i).Font.ColorIndex = 2
            Range("C" & i).Font.Bold = True
        End If
    End If
Next i

End With

Upvotes: 1

Related Questions