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.
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.