I currently use data validation in cell A2 that allows the user to
select 1 of 5 options from a drop-down list (Customer, Representative,
Vendor, etc). I have a second validation list (Yes, No) in cell B2 that
should only be used if "Representative" was selected in A2. Both
work like a charm.

My problem occurs when A2 is changed from "Representative" to
something else. Is there a way to automatically delete the Yes or No in
B2 when A2 is changed?

E.g., the user selects "Representative" in A2 and "Yes" in B2.
Later, the user changes A2 to "Customer." I would like to have the
"Yes" (or "No") in B2 deleted automatically. I thought about using
some sort of If/Then formula in B2, but then I lose the option of the
user selecting Yes or No when "Representative" is selected (or
re-selected) in A2. Any suggestions?

This would require that the data validation set up in B2 (Yes/No) has the
"Ignore Blank" option selected. Code works when any change is made in
selection in A2:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range

Set iSect = Application.Intersect(Range(Target.Address), Range("A2"))
'the belt: did change occur in A2?
If iSect Is Nothing Then
Exit Sub
End If
'suspenders with the belt
'did many things suddenly change including A2?
If iSect.Cells.Count 1 Then
Exit Sub ' only on single cell changes
End If
Range("B2") = ""
End Sub

if you need help getting the code into the right place, maybe this will
provide it to you: http://www.jlathamsite.com/Teach/WorksheetCode.htm

"AntnyMI" wrote:

I currently use data validation in cell A2 that allows the user to
select 1 of 5 options from a drop-down list (Customer, Representative,
Vendor, etc). I have a second validation list (Yes, No) in cell B2 that
should only be used if "Representative" was selected in A2. Both
work like a charm.

My problem occurs when A2 is changed from "Representative" to
something else. Is there a way to automatically delete the Yes or No in
B2 when A2 is changed?

E.g., the user selects "Representative" in A2 and "Yes" in B2.
Later, the user changes A2 to "Customer." I would like to have the
"Yes" (or "No") in B2 deleted automatically. I thought about using
some sort of If/Then formula in B2, but then I lose the option of the
user selecting Yes or No when "Representative" is selected (or
re-selected) in A2. Any suggestions?