Macro that AutoFilters based on Cell value

[Guest]

I want to write a Macro that will run an autofilter based on a cell value.

Ex:
Let's say cell A1 = red and I have a list of cars with their color beside
it. When I run the macro, I want it to use AutoFilter to show all the red
cars. However, if I change A1 to blue, when the macro is run, I want it to
show all the blue cars.

Thanks

 

[Neil Kennedy]

The following line in your macro will do the trick:-

Selection.AutoFilter Field:=2, Criteria1:=colour (colour is the variable for
the value of A1 in)

Change the field number as required - if your date filter is on columns E to
X and the colour is in H then the field number becomes 4 etc

I would recommend putting this line in at the start of the macro:-

Selection.AutoFilter Field:=2

Again, where the field number is your column - this will reset all filters
to show all records.

 

[Guest]

Looks like that'd work, but how do I declare a the cell as a variable?
Sorry, I'm new to writing macros.

 

[Neil Kennedy]

We all had to start somewhere

Try something like this:-

Sub Macro1()

Dim colour As String

Selection.AutoFilter Field:=1 ********amend as required

Range("A1").Select
colour = ActiveCell.Value

Selection.AutoFilter Field:=1, Criteria1:=colour ********amend as required

Range("A1").Select

End Sub