Condicionante, bloqueo y eliminación de celdas

Supongo que quieres algo así:

Sub SI()
ActiveSheet.Unprotect
    Cells.Select
    Selection.Locked = False
    Range("F4:F7,H4:H7").Select
    Selection.ClearContents
    Selection.Locked = False
    Range("B4").Select
    Selection.Locked = True
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub
Sub NO()
ActiveSheet.Unprotect
    Cells.Select
    Selection.Locked = False
    Range("B4").Select
    Selection.Locked = False
    Range("F4:F7,H4:H7").Select
    Selection.ClearContents
    Selection.Locked = True
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then
If UCase(Target.Value) = "SI" Then
SI
ElseIf UCase(Target.Value) = "NO" Then
NO
End If
End If
End Sub

Debes de poner el código en la hoja donde vas a hacer esos movimientos.

Hola Daniel:

El proceso ejecuta correctamente, pero hay una pequeñitos adiciones al momento de ejecutar me selecciona toda la hoja hay forma de colocarlo en la celda B2 y si hay posibilidad de colocarlo contraseña.

Muchas gracias por tu tiempo.

Saludos.

Hola Daniel:

Consulta adicional que al ejecutar me bloquee las celdas que he dicho que deben estar bloqueadas, por ejecuta y los deshabilitan.

Saludos,

De acuerdo a lo que pides así queda:

Sub SI()
ActiveSheet.Unprotect "aqui pones tu contraseña"
    Cells.Select
    Selection.Locked = False
    Range("F4:F7,H4:H7").Select
    Selection.ClearContents
    Range("B4").Select
    Selection.Locked = True
    ActiveSheet.Protect "aqui pones tu contraseña"
    Range("B2").Select
End Sub

Tengo que seleccionar la hoja para poder aplicar el bloqueo posteriormente.

Se borra la información de los rangos F4:F7 y H4:H7 si no es lo que querías solo hay que remplazarlo, se bloquea la celda B4.

Sub NO()
ActiveSheet.Unprotect "aqui pones tu contraseña"
    Cells.Select
    Selection.Locked = False
    Range("F4:F7,H4:H7").Select
    Selection.ClearContents
    Selection.Locked = True
    ActiveSheet.Protect "aqui pones tu contraseña"
    Range("B2").Select
End Sub

Pasa casi lo mismo solo que se seleccionan los rangos F4:F7 y H4:H7 se elimina el contenido y se bloquea.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then
If UCase(Target.Value) = "SI" Then
SI
ElseIf UCase(Target.Value) = "NO" Then
NO
End If
End If
End Sub

Se llama la macro según sea el caso.