CODEWITHSUNDEEP
vbaexcel
cucaracha21
cucaracha21

Reputation: 15

Excel macro - type mismatch error

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

Upvotes: 1

Views: 154

Answers (1)

Fratyx
Fratyx

Reputation: 5797

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

Upvotes: 1

Related Questions