Locking Cells Based on Drop Down

[Raven Jess]

Good morning:

I am trying to lock cells G10 and H10 if cell F10 contains anything other than no from the drop down.

I want to do this for each line, 10-19.

All cells on the worksheet should be locked other than B10:B19 thru I10:I19 and I1:14 thru L1:L4.

Can this be done?

Thanks!

 

[StephenCrump]

Welcome to the Forum!

Perhaps something like this:

'In the relevant sheet module
Sub Initialise()    'or do this manually to start

    With Me
        .Unprotect Password:="YourPassword"
        .Cells.Locked = True
        .Range("B10:I19").Locked = False
        .Range("I1:L4").Locked = False
        .Protect Password:="YourPassword"
    End With
    
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim r As Range
    
    Me.Unprotect Password:="YourPassword"
    
    For Each r In Intersect(Target, Range("F10:F19"))
        Range("G" & r.Row).Resize(, 2).Locked = UCase(r.Value) <> "NO"
    Next r
        
    Me.Protect Password:="YourPassword"

End Sub

 

[Raven Jess]

Thank you very much for your response. When I attempt to use this code, I am getting the error: "Run-time error '424': Object required".

I am not sure how to fix this.

 

[StephenCrump]

Sorry, I should have tested the code ...

Try:

'In the relevant sheet module
Sub Initialise()    'or you could initialise manually in Excel 

    With Me
        .Unprotect Password:="YourPassword"
        .Cells.Locked = True
        .Range("B10:I19").Locked = False
        .Range("I1:L4").Locked = False
        .Protect Password:="YourPassword"
        Call Worksheet_Change(.Range("F10:F19"))
    End With
    
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim rngMonitored As Range, r As Range
    Set rngMonitored = Intersect(Target, Range("F10:F19"))
    
    If Not rngMonitored Is Nothing Then
        Me.Unprotect Password:="YourPassword"
        For Each r In Intersect(Target, Range("F10:F19"))
            Range("G" & r.Row).Resize(, 2).Locked = UCase(r.Value) <> "NO"
        Next r
        Me.Protect Password:="YourPassword"
    End If
    
End Sub