Lock cells if multiple textboxes meet condition

Question

I'm working on a dashboard which is used to calculate the price of panels. The user will input various details about the purchase including budget and stream to determine the overall price at the end.

One of the features is that if the user has a budget (cell E8) of > 50000 AND stream (cell e10) is agency or independent OR if budget is > 35000 AND stream is boutique or direct, it unlocks discount PACKS which can be selected in dropdowns between cells E23:AR23.

I'm looking for a method that will lock cells E23:AR23 unless the criteria above has been inputted. I've tried adding VBA which would lock cells if that criteria was not met but it seems to lock cells and not unlock them when the criteria is met after making further changes.

Any help or alternative methods are welcome.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim rngLock As Range
    Dim rngClear As Range
    Dim cellE8Value As Double
    Dim cellE10Value As String

    ' Define the worksheet
    Set ws = ThisWorkbook.ActiveSheet ' Change to the specific sheet if needed

    ' Define the ranges
    Set rngLock = ws.Range("E23:AR23")
    Set rngClear = ws.Range("E23:AR23")

    ' Get the value of cell E8
    cellE8Value = ws.Range("E8").Value
    
    ' Get the value of cell E10
    cellE10Value = ws.Range("E10").Value

    ' Disable worksheet protection temporarily
    On Error Resume Next
    ws.Unprotect
    On Error GoTo 0

    ' Check if the changed cell is E8
    If Not Intersect(Target, ws.Range("E8")) Is Nothing Then
        ' Lock the range E23:AR23 if conditions are met
        If (cellE8Value < 50000 And (cellE10Value = "Agency" Or cellE10Value = "Independent" Or cellE10Value = "")) _
            Or (cellE8Value < 35000 And (cellE10Value = "Boutique" Or cellE10Value = "Direct" Or cellE10Value = "")) _
            Or cellE10Value = "" Then
            ' Lock the range E23:AR23
            rngLock.Locked = True
            ' Clear the contents of the range E23:AR23
            rngClear.ClearContents
        Else
            ' Unlock the range E23:AR23
            rngLock.Locked = False
        End If
    End If

    ' Protect the worksheet to enable cell locking
    ws.Protect UserInterfaceOnly:=True
    End Sub

Answer 1

• If Not Intersect(Target, ws.Range("E8")) Is Nothing Then only catches the change on E8. That is, change on E10 doesn't tigger the code to unlock the range.

• Utilize Me within the Change event to refer to the sheet containing the event code.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngLock As Range
    Dim cellE8Value As Double
    Dim cellE10Value As String
    ' Disable worksheet protection temporarily
    On Error Resume Next
    Me.Unprotect
    On Error GoTo 0
    ' Check if the changed cell is E8
    If Target.Address(0, 0) = "E8" Or Target.Address(0, 0) = "E10" Then
        ' Define the ranges
        Set rngLock = Me.Range("E23:AR23")
        ' Get the value of cell E8
        cellE8Value = Me.Range("E8").Value
        ' Get the value of cell E10
        cellE10Value = Me.Range("E10").Value
        ' Lock the range E23:AR23 if conditions are met
        If (cellE8Value < 50000 And (cellE10Value = "Agency" Or cellE10Value = "Independent" Or cellE10Value = "")) _
            Or (cellE8Value < 35000 And (cellE10Value = "Boutique" Or cellE10Value = "Direct" Or cellE10Value = "")) _
            Or cellE10Value = "" Then
            ' Lock the range E23:AR23
            rngLock.Locked = True
            ' Clear the contents of the range E23:AR23
            Application.EnableEvents = False
            rngLock.ClearContents
            Application.EnableEvents = True
        Else
            ' Unlock the range E23:AR23
            rngLock.Locked = False
        End If
    End If
    ' Protect the worksheet to enable cell locking
    Me.Protect UserInterfaceOnly:=True
End Sub