Locking a specific cell range based on a drop-down selection

Iron Contributor

Excel drop-down lock cells.png

 

 

I would like to create a drop-down list in cell L7 (pending/approved) that would unlock/lock a specific cell range (A6:K8). The goal is that once the data is approved, it cannot be modified. I found a code in another thread that locks 1 cell but I don't know how to do it with a cell range. Here is the code from that thread:

 

Public OldValue

Private Sub Worksheet_Change(ByVal Target As Range)
If ((Target.Address = "$A$1") And (Target.Value = "yes")) Then Range("B1").Interior.ColorIndex = xlNone

If ((Target.Address = "$A$1") And (Target.Value = "no")) Then
OldValue = Range("B1")
With Range("B1").Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If

If ((Target.Address = "$B$1") And (Range("A1") = "no")) Then
Application.EnableEvents = False
Target.Value = OldValue
Application.EnableEvents = True
End If
End Sub

 

Thanks for the help!

1 Reply

I was able to find another code that does what I want. However, when I try to modify it to fit my needs, it doesn't work. Here is the code:

 

Excel drop-down lock cells v2.png

 

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("C1")) Is Nothing And Target.Value = "No" Then
Worksheets("Sheet1").Protect Password:="Password"
Else
If Not Intersect(Target, Range("C1")) Is Nothing And Target.Value = "Yes" Then
Worksheets("Sheet1").Unprotect Password:="Password"
End If
End If
Application.ScreenUpdating = True
End Sub

 

How can I change which range of cells are being locked by this code? Thanks.