unlock cell based on another

hi i have a spreadsheet that has many rows, if in cell a1 = yes i want to lock cell b2 if not yess then unlock b2. this must then work for each row eg a2,a3 a4 to b2,b3,b4. how can this be achieved is it a code or a macro i have no idea please help

3 thoughts on “unlock cell based on another

  1. Excel Geek

    Hi there. Which version of Excel are you using? I think this will be best completed with a macro.

  2. andrew walker Post author

    Try this macro…

    Sub LockLoop()

    Application.ScreenUpdating = False

    ActiveSheet.Unprotect Password:="admin"
    Selection.Locked = False

    For A = 1 To 10 Step 1
    If Cells(A, 1) = "Yes" Or Cells(A, 1) = "yes" Then
    Cells(A, 2).Locked = True
    End If
    Next A

    ActiveSheet.Protect Password:="admin"

    Application.ScreenUpdating = True

    End Sub

    This code will go down column A and check each cell. If it contains “Yes” or “yes” it will lock the corresponding cell in column B.

    Currently this code will loop ten times. Where it says

    For A = 1 To 10 Step 1

    Change the 10 to the number of rows you want.

    Change the password admin to whatever you want the password to be.

    I suggest you make a back up of you work before testing this or any other macro. It is good practice.

Leave a Reply

Your email address will not be published. Required fields are marked *