Excel macro - type mismatch error

Question

im a noob.

My macro adds a date when a cell value changes to "Closed". Specifically, when a cell value in column M changes to "Closed", it adds the date 2 cells to the left, in column K. Works perfectly, until i edit more than one cell in either column. If i do that, i get a 13 type mismatch error.

This sucks as it means an error comes up each time i autofill.

Click for image of problem...

thanks in advance.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 13 And Target = "Closed" Then
        Target.Offset(0, -2) = Format(Now(), "yyyy-mm-dd")
    End If
End Sub

Fratyx Fratyx · Accepted Answer · 2015-11-24T11:09:05

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    For Each cell In Target
        If cell.Column = 13 And cell = "Closed" Then
            Target.Offset(0, -2) = Format(Now(), "yyyy-mm-dd")
        End If
    Next cell
End Sub

Excel macro - type mismatch error

1 Answers