Unlock cell on a condition from adjacent cell

Question

I have two columns but the codition I would like is to be evaluated from one cell to another.

The first column has cells which have a drop down validation with names, and the second will activate only if a certain name from the adjacent cell is selected.

so far i only found this code but it does not seem to work:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("A1").Value = "Car" Then
        Range("B1").Locked = False
    Else
        Range("B1").Locked = True
    End If
End Sub

I would need this code go from (for example) A1:A10 and B1:B10.

I hope I am making sense. If there is a way to do it without VBA, that would be great.

Thanks for the help.

Answer 1

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("A1").Value = "Car" Then
    Range("B1").Locked = False
    Me.Unprotect ("password")
Else
    Range("B1").Locked = True
    Me.Protect ("password")
End If

End Sub

Use Me.Protect so the .Locked method does something. You should probably unlock every other cell though.

Answer 2

That code snippet works perfectly for me.

Did you place that code in the proper WorkSheet object? It won't work if you just put it into a VBA module. When you are in the Visual Basic Editor, look for a directory on the left side of the screen labeled "Microsoft Excel Objects". In that directory should be a WorkSheet object for every sheet in your file. Double-click on one of these to edit the code for that WorkSheet. This is where your code snippet should go.

Answer 3

The Target parameter tells you the range that is being changed.

You need to do something like the following:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range

    Set rng = Intersect(Range("A1:A10"), Target)
    If rng Is Nothing Then
        ' Not updating the range we care about
        Exit Sub
    Else
        rng.Offset(0, 1).Locked = ... whatever ...
    End If
End Sub

Note that your target range can be more than one cell (e.g. when using copy/paste), so you need to handle and test this case.

Calling Intersect returns you the intersection of the target range and the range you are interested in testing (A1:A10 in this sample).

You can then access the corresponding adjacent cell(s) using .Offset(0,1)