Three Cases
 

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



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
.Offset(1).EntireRow.Insert xlShiftDown
End If
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

Three Cases
 

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


--
HTH

Bob Phillips

"teresa" wrote in message
...
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



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
.Offset(1).EntireRow.Insert xlShiftDown
End If
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub