Trouble adding rows with code

[al97233]

Awhile back Val was kind enough to write the following code for me which has worked very well up till now.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 3 And Target.Row = Me.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row - 2 And Target.Count = 1 And IsEmpty(Target.Value) = False Then
        Application.EnableEvents = False
        
        Me.Range("A" & Target.Row + 1).EntireRow.Insert
        
        With Me
            .Range("A9:D9").Copy
            .Range("A" & Target.Row + 1 & ":D" & Target.Row + 1).PasteSpecial Paste:=xlPasteFormats
            .Range("D" & Target.Row + 1).Formula = "=A" & Target.Row + 1 & " * C" & Target.Row + 1
            .Range("A" & Target.Row + 1).Select
        End With
        
        Application.CutCopyMode = False
        
    End If
    Application.EnableEvents = True

the problem I am having is that I now need to insert several rows into the work sheet and when I do the code no longer works properly. Unfortunately I do not have the knowledge to know how to revise this code to keep it working properly. Another problem I have is I would like to lock most of the cells and protect the work sheet so that I could tab through the appropriate cells rather than have to click through them with a mouse.

Any help with this would be greatly appreciated.

 

[Fluff]

As far as I know there is no way of setting the tab order.
Row 10 will be locked after you enter a value in C10 & row 11 will become unlocked.

 

[al97233]

Thank you to all of you that helped me with this. I truly appreciate it.

 

[Fluff]

Glad we could help & thanks for the feedback