Disable cells

[rahuldev31]

if i select an active cell as I9, then I4 to I8 . Likewise if i select another active cell in I, then need to freeze previous 5 cells from being cut,copy,paste or being edited

 

[Logit]

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim rngProtect As Range
    Dim startRow As Long
    Dim endRow As Long

    ' Set the worksheet
    Set ws = Target.Worksheet

    ' Check if the change occurred in column I and the row is greater than or equal to 6
    If Target.Column = 9 And Target.Row >= 6 Then
        ' Calculate the range of the previous 5 cells directly above the selected cell
        startRow = Target.Row - 5
        endRow = Target.Row - 1
        Set rngProtect = ws.Range("I" & startRow & ":I" & endRow)

        ' Disable events to avoid recursion
        Application.EnableEvents = False

        ' Unprotect the sheet
        ws.Unprotect Password:="123" ' Replace "123" with your desired password

        ' Unlock all cells in the sheet
        ws.Cells.Locked = False

        ' Lock the cells in the specified range
        rngProtect.Locked = True

        ' Protect the sheet to enforce locking
        ws.Protect Password:="123", AllowSorting:=True, AllowFiltering:=True

        ' Re-enable events
        Application.EnableEvents = True
    End If
End Sub