Automatically Autofilter With Cell Value On Selection

[DCollison]

I would like to use the autofilter function in an excel spreadsheet, with the filter value being a cell reference.

Per the following thread http://www.ozgrid.com/forum/showthread.php?t=23186 I created the routine in the attached spreadsheet. I have no experience with visual basic. The routine works when I execute "run/run sub/user form" from the Microsoft visual basic toolbar. However, I don’t know how to execute the routine automatically when a user enters a value in the cell in question.

Alternatively, is there an easier way to accomplish the same using a formula or function?

Thanks

[AAE]

You need to put your code in the worksheet module, Selection Change event.

Copy and paste the code below to the worksheet module, which you can quickly access by right-clicking on the sheet tab and selecting View Code from the context menu.

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
     
    ActiveSheet.AutoFilterMode = False 
    Range("A3:D3").AutoFilter 
    Range("A3:D3").AutoFilter Field:=2, Criteria1:=Range("A2").Text 
     
     
End Sub 

[DCollison]

Thanks! THis was extremely helpful! Daniel

[eyanosa]

AAE,

What would I have to change in the above code to add to the autofilter a greater than and less than filter based on a value in another cell?

Thanks.

[Phaeton]

eyanosa,

You would need to add just one more line for each additional filter

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
     
    ActiveSheet.AutoFilterMode = False 
    Range("A3:D3").AutoFilter 
    Range("A3:D3").AutoFilter field:=2, Criteria1:=Range("A2").Text 
    Range("A3:D3").AutoFilter field:=3, Criteria1:=">" & Range("B2").Value 'greater than filter to Contract ("B2" as criteria)
    Range("A3:D3").AutoFilter field:=4, Criteria1:="<" & Range("C2").Value 'less than filter to Op Code ("C2" as criteria)
End Sub