Unlocking/Locking cells based on value of another cell

[gator2]

I'm trying to lock or unlock cells based on the value of another cell, but I keep running into an error and need some assistance.

I've created a spreadsheet with a data validation list in column J. When the value in column J is "Default", the adjacent cells K-M are locked. If the user needs to change the default values in K-M, the user can unlock these cells by changing the drop down list in column J to "Override Values".

I'm using a custom data validation for columns K-M, =$J$17:$J$64<>"Default". The cells are being locked/unlocked using the following VBA code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("J17") = "Override Credit %" Then
Range("K17:M64").Locked = False
ElseIf Range("J17") = "Default" Then
Range("K17:M64").Locked = True
End If
End Sub

Everything works when there is no other data in the spreadsheet, but as soon as I enter data and try to change a value in columns K-M I get the following error:

"This value doesn't match the data validation restrictions defined for this cell".

I can't seem to figure out the why. Can anyone tell me what I'm doing wrong?

Thanks in advance for your assistance.

 

[Sharid]

See if this will help Stack Over Flow also use the <vba/> tags, when posting code.

 

[Joe4]

Are ALL rows looking at cell J17, or do you want each individual row to look at column J in that particular row, i.e.
- K17-M17 will look at J17
- K18-M18 will look at J18
- K19-M19 will look at J19
etc

Also, I am not sure what you are trying to do with this:

I'm using a custom data validation for columns K-M, =$J$17:$J$64<>"Default".

You use Data Validation to limit the possible entries in those cells (i.e. a list of possible values they can select from).
It does not work like Conditional Formatting.
If I understand your question, I don't think it is necessary to what you are trying to do here.

 

[gator2]

See if this will help Stack Over Flow also use the <vba/> tags, when posting code.

Thank you Sharid. I will check out the article and see if it helps.

 

[gator2]

Are ALL rows looking at cell J17, or do you want each individual row to look at column J in that particular row, i.e.
- K17-M17 will look at J17
- K18-M18 will look at J18
- K19-M19 will look at J19
etc

Also, I am not sure what you are trying to do with this:

You use Data Validation to limit the possible entries in those cells (i.e. a list of possible values they can select from).
It does not work like Conditional Formatting.
If I understand your question, I don't think it is necessary to what you are trying to do here.

Hi Joe4, thanks for your assistance. Each row should be evaluated on it's own, so your example (K17-M17 will look at J17...) is correct.

I tested removing the custom data validation from K-M, but the cells become unlocked regardless of the value in J.

 

[Joe4]

Also note that locking/unlocking cells does nothing without sheet protection (can do it with or without a password).
All cells are, by default, locked. So if you turn on sheet protection, ALL cells will be locked.
Typically what people do is highlight the whole sheet, and then unlock the cells, then protect the sheet. Then the code should do what you want.

Here is the code I came up with for you. I added lots of comments (in green) to explain what those steps are doing.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    Dim rw As Long
   
'   See if any cells updated in column J, if not exit
    Set rng = Intersect(Target, Range("J17:J64"))
    If rng Is Nothing Then Exit Sub
   
'   Loop through values in column J just updated
    For Each cell In rng
'       Get row number
        rw = cell.Row
'       Unprotect sheet
        ActiveSheet.Unprotect
'       Check value
        Select Case cell.Value
            Case "Override Credit %"
                Range("K" & rw & ":M" & rw).Locked = False
            Case "Default"
                Range("K" & rw & ":M" & rw).Locked = True
        End Select
'       Protect sheet
        ActiveSheet.Protect
    Next cell

End Sub