Automatically insert a new row when certain number is entered in c

Q

Question, Mark

I would like a new row automatically inserted under each cell in a worksheet
that receives a .5 entry. Any ideas? The only entries allowed in the cells
are 1 or .5
 
S

Sheeloo

Right-click on the sheetname at the bottom of the screen and
paste the following code

Private Sub Worksheet_Change(ByVal Target As Range)
'Do nothing if more than one cell is changed or content deleted
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
'Check whether the cell value is 0.5
If Target.Value = 0.5 Then
'Insert a row below the cell with value 0.5
Target.Offset(1, 0).EntireRow.Insert
End If
End Sub

Now a row will be inserted below the cell if you enter 0.5 in any cell

If you want to insert only a cell then use
Private Sub Worksheet_Change(ByVal Target As Range)
'Do nothing if more than one cell is changed or content deleted
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
'Check whether the cell value is 0.5
If Target.Value = 0.5 Then
'Insert a row below the cell with value 0.5
Target.Offset(1, 0).Insert Shift:=xlDown
End If
End Sub
 
Top