Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long, lr As Long

    If Target.CountLarge <> 1 Then Exit Sub
    If Target.Column <> 5 Then Exit Sub
    r = Target.Row: lr = Cells(Rows.Count, 3).End(xlUp).Row
    ActiveSheet.Unprotect
    Select Case Target.Value
        Case "Yes"
            Range(Cells(r, 3), Cells(r, 4)).Locked = True
            Target.Locked = False
        Case "No"
            If Range(Cells(r, 3), Cells(r, 4)).Locked Then Range(Cells(r, 3), Cells(r, 4)).Locked = False
    End Select
    Range(Cells(r + 1, 3), Cells(lr + 1, 4)).Locked = False
    ActiveSheet.Protect Password:="123"
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge <> 1 Then Exit Sub
    If Target.Column <> 5 Then Exit Sub
    Select Case Target.Value
        Case "No"
            Target.EntireRow.Hidden = True
    End Select
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then
    With Range(Cells(Target.Row, 3), Cells(Target.Row, 4)).Validation
    .Delete
    If Target.Value = "No" Then
        .Add Type:=xlValidateCustom, Formula1:="="""""
        .ErrorTitle = "Запрещено редактировать!"
        .ErrorMessage = "Ввод новых значений запрещён" & vbCrLf & "Нажмите ""ОТМЕНА"""
        .ShowError = True
    End If
    End With
End If
End Sub