Forum Discussion

Jason Drew's avatar
Jason Drew
Iron Contributor
Jun 14, 2018

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

 

 

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!

developer
excel
  • Jason Drew's avatar
    Jason Drew
    Iron Contributor
    Jun 14, 2018

    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:

     

     

    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.

Resources