David
asked on
I would like to add the following to the VBA code: if J19 or J22 is "Yes", then change J41:J45 to "NA".
I would like to add the following to the below VBA code: if J19 or J22 is "Yes", then change J41:J45 to "NA".
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
On Error GoTo Skip
Application.EnableEvents = False
If Not Intersect(Target, Range("J19:J21")) Is Nothing Then
Select Case Target.Address(0, 0)
Case "J19"
If Target = "<Select>" Then
Range("J20:J21").Value = "<Select>"
ElseIf Target = "No" Then
Range("J20:J21").Value = "NA"
ElseIf Target = "Yes" Then
Range("J20:J21").Value = "<Select>"
End If
Case "J20", "J21"
If Target = "" Or Range("J19") = "No" Then
Range("J19:J21").Value = "<Select>"
End If
End Select
ElseIf Not Intersect(Target, Range("J22:J24")) Is Nothing Then
Select Case Target.Address(0, 0)
Case "J22"
If Target = "<Select>" Then
Range("J23:J24").Value = "<Select>"
ElseIf Target = "No" Then
Range("J23:J24").Value = "NA"
ElseIf Target = "Yes" Then
Range("J23:J24").Value = "<Select>"
End If
Case "J23", "J24"
If Target = "" Or Range("J22") = "No" Then
Range("J22:J24").Value = "<Select>"
End If
End Select
End If
Skip:
Application.EnableEvents = True
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
On Error GoTo Skip
Application.EnableEvents = False
If Not Intersect(Target, Range("J19:J21")) Is Nothing Then
Select Case Target.Address(0, 0)
Case "J19"
If Target = "<Select>" Then
Range("J20:J21").Value = "<Select>"
ElseIf Target = "No" Then
Range("J20:J21").Value = "NA"
ElseIf Target = "Yes" Then
Range("J20:J21").Value = "<Select>"
End If
Case "J20", "J21"
If Target = "" Or Range("J19") = "No" Then
Range("J19:J21").Value = "<Select>"
End If
End Select
ElseIf Not Intersect(Target, Range("J22:J24")) Is Nothing Then
Select Case Target.Address(0, 0)
Case "J22"
If Target = "<Select>" Then
Range("J23:J24").Value = "<Select>"
ElseIf Target = "No" Then
Range("J23:J24").Value = "NA"
ElseIf Target = "Yes" Then
Range("J23:J24").Value = "<Select>"
End If
Case "J23", "J24"
If Target = "" Or Range("J22") = "No" Then
Range("J22:J24").Value = "<Select>"
End If
End Select
End If
Skip:
Application.EnableEvents = True
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much!
You're welcome!
ASKER