How to make code run when a cell is selected

[Lorna]

I have a sheet which contains four dependent dropdown boxes. The first three work just fine using the Indirect function and Named Ranges. The fourth dropdown contains illegal characters and there is so much data that is seems too cumbersome to use the VLookUp option to work around it. So what I'm trying to do is to use an advanced filter to generate the data based upon selections from the previous three dropdowns. The data that it's filtering is located on another sheet called Validation Lists. I created a macro to do this and attached it to a button by the fourth dropdown and it works just fine to filter the data appropriately for the dropdown. This seems like a Mickey Mouse way to do it, however.

What I want to have happen is that when the user clicks on cell C23 where the third dropdown located, I want the code to run that filters the data for the fourth dropdown located in cell C27. (This dropdown uses as its source a range called OtherFiltered which refers to the column of extracted data on the Validation Lists sheet where the filtered data is located.)

The code I have attached for the worksheet where the dropdowns are located doesn't produce any errors, it just doesn't do anything but clear the contents of all the dropdowns (as it's supposed to) and clears the previously range of previously extracted (if there is any) on the Validation Lists sheet. The section of code that performs the advanced filter never runs. What am I doing wrong?!!!

Following is the code I'm trying to use. Any help as to why it's not working would be greately appreciated.


Private Sub Worksheet_Change(ByVal Target As Range)
'Clears contents of dependent dropdown boxes when data changes
If Not Intersect(Target, Range("C3")) Is Nothing Then
Sheets("Food Rotation").Range("C19").Value = ""
End If
If Not Intersect(Target, Range("C19")) Is Nothing Then
Sheets("Food Rotation").Range("C23").Value = ""
End If
If Not Intersect(Target, Range("C23")) Is Nothing Then
Sheets("Food Rotation").Range("C27").Value = ""
End If
'
'Runs only when value in Other Criteria dropdown box changes
On Error Resume Next
If Not Intersect(Target, Range("C23")) Is Nothing Then
Application.Goto Reference:="OtherClearRange"
Selection.ClearContents
Range("OtherData").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Range("OtherCriteria"), CopyToRange:=Range("OtherExtract"), Unique:=True
Sheets("Food Rotation").Select 'Location of fourth dropdown
Range("C27").Select
End If
End Sub