lock select cells

[hotdogs1999]

Hello all,

looking for a way to lock a select number of cells, regardless of what rows are added above it.

am working with a table, and will always be adding rows at the top of the table (so all data shifts down a row).

the data that i want to lock in place is actually outside of the table. (beside it).

the cells i am trying to lock are N3:N9, and O3:O9.

so if i add rows at the top of the table/sheet, theses cells will always stay in N3:N9, and O3:O9, and won't shift down with the rest.

can use code/macros if necessary.

thanks in advance.

 

[DanteAmor]

Excute the next macro to insrt rows.


To lock the cells it is necessary to protect the sheet. Change the macro "abc" by the password you want to protect the sheet.
Change the number 2 in the macro by the row number where you perform the row insertion.

Sub Insert_Rows()
'
    fila = 2        'row number where the rows are inserted
    pwd = "abc"     'password to protect sheet
    '
    wrow = InputBox("number of rows to insert : ", "INSERT ROWS")
    If wrow = "" Or Not IsNumeric(wrow) Or wrow < 1 Then
        Exit Sub
    End If
    ActiveSheet.Unprotect pwd
    Cells.Locked = False
    Rows(fila & ":" & fila + Val(wrow) - 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("N3:O9").Locked = True
    ActiveSheet.Protect pwd, _
        DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub