Refresh Autofilter Upon Cell Value Change

[GA3]

I have a spreadsheet with a drop down box that has dates in it in C1.

Below that I have data that I need to filter based on the date in the drop down box occurring between 2 dates listed in the data below it.

I have already taken care of the date ocurring between the 2 dates by writing a formula that returns a simple "YES/NO" as a result. "YES/NO" is located in column N starting with header in row 4 and data in row 5.

My issue is getting the list to refilter using autofilter to "YES" whenever the Date in C1 is changed.

TIA for any help.

 

[Weaver]

Right click the tab, choose 'view code' and paste the following:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$1" Then
        Range("n4").CurrentRegion.AutoFilter Field:=14, Criteria1:="yes"
    End If
End Sub

HTH

 

[GA3]

That works perfectly! THANK YOU!

 

[GA3]

Ok... Let's fast forward 3 years... I have been requested to update this to include some additional data. I now have a Category column in column z. I have created a drop down with data with all the categories involved in cell C2.

This code is attached to this sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If

If Target.Address = "$C$1" Then
Range("n4").CurrentRegion.AutoFilter Field:=14, Criteria1:="yes"
End If

End Sub

This gives me what I needed originally...when I change the date in C1 it refilters the sheet to reflect things that occur during the date's time frame.

I now need it to refilter based on date AND THEN the category chosen, and I need it to do it everytime the category is changed.

If the date is changed then it should reset the entire sheet.

Can anyone please help me?