lock/unlock cell based on value in other cell

[Laurens1]

Hi Guys,

I want to lock fields when a specific cell is empty and unlock it when that specific cell contains a value.

Example:

	A	B	C	D	E
1	Fruit	Banana	Pear	Orange	Apple
2	Apple	locked	locked	locked	unlocked
3	Banana	unlocked	locked	locked	locked
4	Pear	locked	unlocked	locked	locked
5	Orange	locked	locked	unlocked	locked

<colgroup><col><col span="5"></colgroup><tbody>
</tbody>

Columns B:E should always be locked. But when Cell A2 is Apple, I want to unlock the cell in the apple column (E) on the same row.

Is this possible, and if yes... How?

 

[CalcSux78]

Welcome to the forum!

Since you're wanting to change the Locked status of cells pragmatically, I think you'll need some code. Let us know if you need help placing it.

Right click your sheet containing the list and select "View Code" Paste and close Visual Basic.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim cel As Range
Dim r&, c&      'last row & column variable
Dim fCol$, fRow$    'label in title row/column


r = Cells(Rows.Count, 1).End(xlUp).Row
c = Cells(1, Columns.Count).End(xlToLeft).Column


'execute when change event is in first column
If Not Intersect(Cells(1, c), Target) Is Nothing Then
    Set rng = Range([B2], Cells(r, c))
    
    For Each cel In rng.Cells
        fCol = Cells(cel.Row, 1)
        fRow = Cells(1, cel.Column)
        If fCol = fRow Then
            cel.Locked = False
        Else
            cel.Locked = True
        End If
    Next cel
End If
End Sub

The code will run when there is a value changed in column A.