Intersect

[ElvisSteel]

I have an input sheet, where column A has a dropdown list.
Column B has a dropdown list that is dependent on the value selected in column A, using the INDIRECT function.
One of the problems is that if the user then changes the entry in column A, I need to clear the entry in column B so that the entry has to be reselected.
I want to use WorksheetChange to trigger this, but I need some help in making sure that when a cell in column A is changed, I only reset the cell in column B on the same row.
I am sure that this can be done using the INTERSECT function, and I have got it working for a single row, but I don't want to just repeat the code 100 times (there are 100 rows).
Can anyone help

Thanks

 

[jbeaucaire]

Option Explicit
Public Prior As String

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("A:A")) Is Nothing Then _
    If Target <> Prior Then Range("B" & Target.Row).ClearContents

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Prior = Range("A" & Target.Row)
End Sub

These two codes will work together to clear B if A actually changes. If you just make the same selection in the drop box that was there before, it won't clear the cell.

You can change the A:A to A1:A100 to restrict the activity only 100 rows...

 

[ElvisSteel]

Thanks very much for the prompt response!