How to clear contents of a cell when another is changed (VBA)

[ahungria]

Hi,
I am using drop down boxes (validation) and i want to clear the contents of 1 drop down cell when another drop down cell changes. Below is a code that works when i just click on the cell, but i want to modify it to be when a change occurs, not just with a click.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If Target.Address = "$D$17" Then <-- this is the cell that is changed
Range("D21").ClearContents <-- this is the cleared cell
End If
End Sub

Im using Excel 2003. Thank you for the help!

 

[jbeaucaire]

Try this:

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

    For Each Cell In Target
        If Cell.Address = "$D$17" Then 
            Application.EnableEvents = False
                Range("D21").ClearContents
            Application.EnableEvents = True
    Next Cell

End Sub

 

[ahungria]

Thank you for the code, but i got an error:

Compile Error:
Next Without For

Any ideas?

 

[jbeaucaire]

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

    For Each Cell In Target
        If Cell.Address = "$D$17" Then 
            Application.EnableEvents = False
                Range("D21").ClearContents
            Application.EnableEvents = True
        End If
    Next Cell

End Sub

 

[ahungria]

That worked perfectly! Thank you very much for the help!!!