Three Cases

[Guest]

Hi,

Im trying to write code so that if i

1) Insert a value in col A a row is inserted below
2) If i then go back and delete this value the row below is deleted
3) If I go back and change the value the no of rows remain

With the code below rows are inserted in all three cases,
many thanks for help

 

[Bob Phillips]

Teresa,

Stick to one thread it makes life simpler to see what has already gone down.

This is difficult, how is the code supposed to know that a row has been
inserted by this code, or that a cell has already been changed. One way
would be to have a column with a value in there, which can handle conditions
1 and 3, but not 2. Best to select both rows to delete that.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo ws_exit:
With Target
If .Column = 1 Then
If .Cells.Count = 1 Then
If .Offset(0, 1).Value <> "Upd" Then
.Offset(0, 1).Value = "Upd"
.Offset(1).EntireRow.Insert xlShiftDown
.Offset(1, 1).Value = "Insert"
End If
End If
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub