Clearing cells when selecting from a drop down list

[MP]

I have cells on the worksheet that when you select an option from a drop down
list you type in a response. I need to clear these cells as a different
option is selected from the drop down list
i.e A1 - drop down list with options
A2 - cell will change with a a question depending upon what was selected
from A1
A3 - has response typed in i.e like the number 4 - this is what I need to
clear when someone goes back to A1 and selects something different

Thanks in advance
MP

 

[T. Valko]

Try this macro:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrHandler
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
Application.EnableEvents = False
Target.Offset(0, 2).ClearContents
End If
ErrHandler:
Application.EnableEvents = True
End Sub

This assumes you have drop downs in the range A1:10. Change range to suit.

To use this, select the sheet where you want this to happen.
Right click on the sheet tab
Select View Code
Paste the above code into the window that opens
Hit ALT Q or close the window by clicking the X to return to Excel.