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
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> >
> >
>