CODEWITHSUNDEEP
excelvba
Guu
Guu

Reputation: 129

Private Sub Worksheet_Change(ByVal Target As Range) when changing multiple cell simultaneously

I have this simple VBA to record when task was started and when it was completed.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim cell As Range

If Target.Column = 3 Then
    For Each cell In Target.Cells

        If Target.Cells = "IN PROGRESS" And Target.Cells.Offset(0, -2).Value = "" Then
            Target.Offset(0, -2) = Format(Now(), "DD-MM-YYYY")

        ElseIf Target.Cells = "DONE" And Target.Offset(0, -1).Value = "" Then
            Target.Offset(0, -1) = Format(Now(), "DD-MM-YYYY")

        End If
    Next cell
End If

End Sub

Sheet looks like:

enter image description here

It works perfectly when changing cells one by one.

However, if I try to paste "DONE" in multiple cells, I get the "Type 13 Error mismatch" and VBA does not execute.

enter image description here

Anyone knows a simple way to solve this?

Upvotes: 1

Views: 2083

Answers (1)

Mr ML
Mr ML

Reputation: 428

You use a for each loop where you go through all the cells in the Target.cells. However you use target.cells inside the loop to check.

If you change your references in the loop from target.cells to cell it should fix your code.

Another note, it can benefit you if you use cell.value. This way you only make a check on the value in the cell.

For Each cell In Target.Cells

    If cell.value = "IN PROGRESS" And cell.Offset(0, -2).Value = "" Then
        cell.Offset(0, -2).value = Format(Now(), "DD-MM-YYYY")

    ElseIf cell.value = "DONE" And cell.Offset(0, -1).Value = "" Then
        cell.Offset(0, -1).Value = Format(Now(), "DD-MM-YYYY")

    End If
Next cell

Upvotes: 3

Related Questions