vba to lock columns b thru d based on value selected in col a

[cm]

This code needs to apply to one row at a time.

Column A - each cell has data validation -- possible values of Yes and No.

If the user chooses 'Yes' in A1, I need to unlock B1 thru D1 (and keep them
that way), otherwise those cells should be locked.

Same thing on row 2, etc, down to possible 300 rows or more.

 

[Gord Dibben]

Assumptions

1. Columns B are locked.

2. Column A is unlocked.

3. Sheet protected with password of "justme" no quotes.

4. User can go back and change "Yes" to "No" which would re-lock the B
cells.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
Me.Unprotect Password:="justme"
n = Target.Row
If Me.Range("A" & n).Value = "Yes" Then
Me.Range("B" & n & "" & n).Locked = False
Else
Me.Range("B" & n & "" & n).Locked = True
End If
End If
enditall:
Application.EnableEvents = True
Me.Protect Password:="justme"
End Sub


Gord Dibben MS Excel MVP