Locking Cells based on another cells value

Hi, Data Validation can help you to do this. But you don't need to enter password. Assume that, you have below data:

- Select B3:B16

- Click on Data | Data Validation | Data Validation

- Select Custom from Settings tab and enter below formula:
=C3<=0

- Now Click on Error Alert tab and set it like below image:

- Click Ok.

Now when you enter a value against greater than or equal to 1 value, than it will show you an error message.

- As per your requirement, First clear C3:C16 cell data.

- Enter all the values of all cells in B3:B16 cell.

- Then enter the Data Value from C3:C16 cell.

- This will allow all data a protection.

- You can change those cells where Data Value is <=0, But you can't change where those cells, where Data Value is Greater than or Equal to 1.

Thanks Faysal.

It is useful but not exactly what I need in this case.

You need VBA to accomplish this.

To give you an idea, the following basic event code monitors cell A1 and if a value is entered into it that is greater than zero it locks cell B1 (along with any other cells on the rest of the sheet that are not specifically unlocked).

This code goes in the relevant Sheet object of your workbook. i.e. in the VBA editor (ALT+F11) double click on the sheet name under Microsoft Excel objects in the top left corner and paste the code in the resultant window.

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.Unprotect
    Cells.Locked = False
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        If Range("A1").Value > 0 Then
            Range("B1").Locked = True
        Else
            Range("B1").Locked = False
        End If
    End If
    ActiveSheet.Protect
End Sub