Activate Code on Change to one of two Dropdown Data Validation objects

[pliskers]

I have two data validation dropdown objects that are interdependent (Region and Market). They are linked to a dynamic list on a separate tab, so that when the Region is selected in Dropdown A, the list of available Markets in dropdown B displays only the correct options.

However, the way it currently works, if a selection has previously been made of a Region and Market, when the Region dropdown is changed, the Market selection remains unchanged, which presents a mismatch of Region-Market.

I would like to attach some code to the sheet so that when the Region dropdown is changed, the previously selected Market dropdown item is cleared out and replaced by "", which is included in my data validation list range.

Hope this is clear, and any help would be appreciated. I assume it's some type of Worksheet Change Event code, but am not clear on the most efficient way to code it.

Thank you!

 

[Fluff]

What cells are the DV in?

 

[pliskers]

What cells are the DV in?

You can just use A1 for Division and B1 for a market. Thanks!

 

[Fluff]

Ok, how about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "A1" Then
      Range("B1").Value = ""
   End If
End Sub

 

[pliskers]

Worked like a charm! Thanks again.

Ok, how about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "A1" Then
      Range("B1").Value = ""
   End If
End Sub

 

[Fluff]

You're welcome & thanks for the feedback.