Using a selection from a Data Validation list to delete a row

[PHSYT]

I am trying to find workarounds for users to insert and delete rows in a protected sheet/table.
I have UserInterFaceOnly:=True and have a simple macro to insert a row at the desired location. This is fine as the row will always insert in the same place, but having a simple macro to delete a row can remove any row (including those that should never be deleted). The best solution i can think of (and one which i like) would be to have a macro that deletes a row if the user selects 'Delete' from a data validation list (of that particular row).

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Column = 1 Then
        ThisRow = Target.Row
        If Target.Value = "Delete" Then
        '    Range("B" & ThisRow).Interior.ColorIndex = 3
        '    Target.Row("1:1").EntireRow.Select
        Target.Rows.EntireRow.Select
            Selection.Delete Shift:=xlUp
        Else
            Range("B" & ThisRow).Interior.ColorIndex = xlColorIndexNone
        End If
    End If
End Sub

the macro successfully deletes the row but keeps throwing an error back at me (operator type mismatch) and highlights 'If Target.Value = "Delete" Then' part of the macro.
I presume it has an issue with the target value now being deleted?
i didnt want to put in something to ignore the error and end if the macro is fundamentally flawed.

Can anyone help me?

 

[PHSYT]

sorry... cleaner VBA code

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Column = 1 Then
        ThisRow = Target.Row
        If Target.Value = "Delete" Then
        Target.Rows.EntireRow.Select
            Selection.Delete Shift:=xlUp
        Else
        End If
    End If
End Sub

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    If Target.Value = "Delete" Then
        Rows(Target.Row).Delete
    End If
    Application.EnableEvents = True
End Sub

 

[Fluff]

Another option

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column = 1 Then
        If Target.Value = "Delete" Then
            Target.EntireRow.Delete
        End If
    End If
End Sub