Option Explicit
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
Select Case Target.Value
Case "No"
With [A2]
.Validation.Delete
.Value = "N/A"
End With
Case "Yes"
[A2] = ""
With [A2].Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="Yes, No"
.IgnoreBlank = True
.InCellDropdown = True
End With
Case Else
With [A2]
.Validation.Delete
.Value = ""
End With
End Select
End Sub