Q:

Protect cell depending on other cell's value

I have an Excel spreadsheet where I want to protect cells in column I if the respective cell of column H <> "yes".
I found a code but it will protect all the cells of column I.
Option explicit

Sub unprotected
Me.unprotect password:= "abc"
End sub

Sub protect
Me.protect userinterfaceonly:= true ,password:= "abc"
End sub

Private Sub Worksheet_change(ByVal Target As Range)

Dim Crow as Long

Call Unprotected

xrow = Target.Row

If not (intersect(Target, range("H3:H1000")) is nothing then
    Cells(xrow, "I").locked = (Ucase(trim(cells(xrow, "H").value))<>"yes")
End if
Call protect
End sub

A:

Try this:
Option Explicit

Const PW As String = "abc" '<< use a constant for fixed/shared values

Private Sub Worksheet_change(ByVal Target As Range)

    Dim rng As Range, c As Range
    'find changed cells in range of interest
    Set rng = Application.Intersect(Target, Me.Range("H3:H1000"))
    If Not rng Is Nothing Then
        UnprotectMe
        'process each cell
        For Each c In rng.Cells
            Me.Cells.Cells(c.Row, "I").Locked = _
                 (UCase(Trim(Me.Cells(c.Row, "H").Value)) <> "YES")
        Next c
        ProtectMe
    End If

End Sub

Sub UnprotectMe()
    Me.Unprotect Password:=PW
End Sub

Sub ProtectMe()
    Me.protect userinterfaceonly:=True, Password:=PW
End Sub

