Recurring formula for new records

Hello,

I have an Excel table. Each row is a separate record. The user types in new records at the end of the table. One of the columns in the table contains a formula. Ideally the formula should apply for each cell in the entire column. Since I don't know how long the table will end up being, I simply ask that the user drag the formula down from the cell above. I do not want the user to accidentally change the formula though. At first I thought a Custom validation with a secret word that the user is unlikely to type in, would keep the user from changing the formula already in the cell. However, I can't stop the user from deleting the formula, which doesn't help. I then thought maybe locking (protecting with password) the column would do the trick. However, now the user can't drag down the formula from the cell above. I would therefor like help with one of 2 options:

1- have the formula apply to the cell automatically as a new record is created (ideal)
2- find a way to protect the cell so that the user can copy the formula down, but not change or delete it

Any brain waves?

I did find the following code online, which appears to go some ways to answer my first option, though I can't figure out why it only works if the formula is in column A (i.e. I have formulas in columns A, B, K, L, and AF to DL).

Option Explicit 
 
Private Sub Worksheet_Change(ByVal Target As Range) 
    Dim r As Range, c As Range, t As Range 
     
    On  Error Goto TheEnd 
     
    If Not  Intersect(Target, [E9:E65536]) Is Nothing Then 'Skips B1.  Titles usually on first 7 rows. Row 8 has first  formulas. Column E has first user typed entry
        MsgBox Intersect(Target, [E9:E65536]).Address & " was changed." 
        Application.EnableEvents = False 
        Application.Calculation = xlCalculationManual 
        Application. ScreenUpdating = False 
         
        For Each t In Target 
            Set r = Union(Range("A" & t.Row), Range("C" & t.Row, Cells(t.Row, 19))) 
            If VarType(t.Value) = vbEmpty Then 
                 'r.ClearContents  'Clear all contents in row where B is empty.
                For Each c In r 'Clear only formulas where B is set to empty.
                    If c.HasFormula Then c.Clear 
                Next c 
                Goto nextt 
            End If 
            For Each c In r 
                If c.Offset(-1, 0).HasFormula Then c.Offset(-1, 0).Copy c 
            Next c 
nextt: 
        Next t 
         
TheEnd: 
        Application.EnableEvents = True 
        Application.Calculation = xlCalculationAutomatic 
        Application.ScreenUpdating = True 
    End If 
     
End Sub