none
Lock cells based on the values of another cell RRS feed

  • Question

  • Hi All,

    Can anyone advice what is the macros for: If cell B2=1, lock C2 and D2, if cell C2=1, lock B2 and if D2=1, lock B2 and C2.

    Thanks.

    Thursday, March 12, 2015 7:44 AM

Answers

  •     1) Copy this code.
        2) Right-Click the sheet tab of interest.
        3) Select "View Code"
        4) Paste the code into the window that appears.

        5) Change the password to your actual password
        6) Save the file as a macro-enabled .xlsm file.
        
       

    Private Sub Worksheet_Change(ByVal Target As Range)

        If Target.Cells.Count > 1 Then Exit Sub
        If Intersect(Target, Range("B2:D2")) Is Nothing Then Exit Sub 'specific range
        If Target.Value <> 1 Then Exit Sub

        Application.EnableEvents = False

        Me.Unprotect "password"

        If Target.Address = "$B$2" Then
            Range("C2:D2").Locked = True
        End If

        If Target.Address = "$C$2" Then
            Range("B2").Locked = True
            Range("D2").Locked = True
        End If

        If Target.Address = "$D$2" Then
            Range("B2:C2").Locked = True
        End If

        Me.Protect "password"

        Application.EnableEvents = True
    End Sub

                            
    • Marked as answer by L.HlModerator Thursday, March 19, 2015 3:19 PM
    Thursday, March 12, 2015 5:36 PM