Protect Individual Cell with Formula?

[Jacob Hilderbrand]

You can get tricky with validation, but the cell won't really be protected. What you need is VBA. Put this code in the sheet code section for the sheet you want it to work on. Change "Sheet1" to the actual sheet name, you can also use ActiveSheet if you prefer.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("A1").Value = 4 Or Range("A1").Value = 5 Then
        Sheets("Sheet1").Unprotect Password:="Password"
        Range("A2").Locked = True
        Sheets("Sheet1").Protect Password:="Password"
    Else
        Sheets("Sheet1").Unprotect Password:="Password"
        Range("A2").Locked = False
        Sheets("Sheet1").Protect Password:="Password"
    End If
End Sub