# can you lock cells if one cell = a certain value



## alethea2022 (Dec 15, 2022)

Hi

We want to log our bugs and features but if you have a bug you may want them to fill out the information in columns b c d e and f and if it is a feature you may want them to fill in columns b c d e, g, h and i

in column a people will select from a drop down if it a bug or feature

Is this possible please?  thank you for your help


----------



## breynolds0431 (Dec 17, 2022)

Hi. See if the below meets your expectations. The below would need to be in the log's sheet module. 


```
Private Sub Worksheet_Change(ByVal Target As Range)

'Active Sheet declaration
Dim ws As Worksheet: Set ws = ThisWorkbook.ActiveSheet
'Finds last row
Dim lrow As Long: lrow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
'Sets column A as range to trigger drop-down changes
Dim kcel As Range: Set kcel = ws.Range("A2:A" & lrow)
'Sets sheet password - update as needed
Dim pStr As String: pStr = "password"

If Not Application.Intersect(kcel, ws.Range(Target.Address)) Is Nothing Then
    'Ensures drop-down is not blank
    If Target.Value = vbNullString Then Exit Sub
        'If Bug, then column F will highlight and be set to unlocked, columns G
        'through I will be locked and shaded gray
        'If Feature, then columns G:I will be highlighted and unlocked, column F
        'will be locked and shaded gray
        With ws
            If Target.Value = "Bug" Then
            .Unprotect pStr
                With .Range("F" & Target.Row)
                    .Locked = False
                    .Interior.Color = 65535
                End With
                With .Range("G" & Target.Row, "I" & Target.Row)
                    .Locked = True
                    .Interior.Color = 14540253
                End With
            ElseIf Target.Value = "Feature" Then
            .Unprotect pStr
                With .Range("G" & Target.Row, "I" & Target.Row)
                    .Locked = False
                    .Interior.Color = 65535
                End With
                With .Range("F" & Target.Row)
                    .Locked = True
                    .Interior.Color = 14540253
                End With
            End If
            .Protect pStr
    End With
End If

End Sub
```


----------



## alethea2022 (Dec 20, 2022)

thank you so much


----------

