Protect Previous (lower) 5 Cells

Question

Let's hope I can describe this so it is understandable.

The following requirement affects Column I.

If the user selects cell I6 or higher and enters data into that cell ... the previous (lower) 5 cells are protected (cells I1:I5). All other cells in the worksheet including I6 are not affected by this locking of cells.

If the user selects any cell in the range of I1:I6 a warning message is generated advising the user to select any cell higher than I6.

After the first cell selection of I6 ... the user then selects any cell higher than I6, the previous (lower) 5 cells from the newly selected cell are protected. All other cells (including the previously protected cells) are now not protected. At all times in this process the selected cell is always unprotected.

Here is a macro that attempts to accomplish the goal but obviously it doesn't. Any assistance provided is greatly appreciated.

Option Explicit

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, 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 = Sh

    ' Check if the selection is a single cell in column I
    If Target.Column = 9 And Target.Cells.Count = 1 Then
        ' If the selected cell is near the top of the sheet, prevent action
        If Target.Row - 5 < 1 Then
            MsgBox "Not enough rows above the selected cell to lock 5 cells.", vbExclamation, "Invalid Selection"
            Exit Sub
        End If

        ' 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:="yourpassword" ' Replace "yourpassword" with your desired password

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

        ' Lock only the 5 cells above the selected cell
        rngProtect.Locked = True

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

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

Answer 1

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