[SOLVED] Filter based on dynamic cell value

Filter based on dynamic cell value

I filter columns of data, A12:I350, based on two cell values: B8 and L10.

The code works but I need to open the VBA module and run it every time I change the cell value. I would like the columns to auto filter once the cell value is changed.

The current code:

Option Explicit
Sub FilterBasedOnCellValueAnotherSheet()
Dim product1, product2 As Range
With Worksheets("Compensation Eval - Template")
Set product1 = .Range("B8")
Set product2 = .Range("L10")
End With
With Worksheets("Compensation Eval - Template")
With .Range("A12:I350")
.AutoFilter Field:=2, Criteria1:=product1, Operator:=xlOr, Criteria2:=product2
End With
End With
End Sub

Following this question and answer: VBA Filter based on another cell

I tried:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row = 2 And Target.Column = 3 Then
    Sub FilterBasedOnCellValueAnotherSheet()
    Dim product1, product2 As Range
    With Worksheets("Compensation Eval - Template")
    Set product1 = .Range("B8")
    Set product2 = .Range("L10")
    End With
    With Worksheets("Compensation Eval - Template")
    With .Range("A12:I350")
    .AutoFilter Field:=2, Criteria1:=product1, Operator:=xlOr, Criteria2:=product2
    End With
    End With
End If
End Sub
End Sub

The C2 cell value (Target.Row = 2 And Target.Column = 3) is changed using a formula that fills from another worksheet.

Due to the information from below I chose to make the field that starts the module upon change J4. This way a new value must be manually entered when the user wants to filter the data. I will also filter the data based on the field J4.

The working code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 4 And Target.Column = 10 Then
        Dim product1 As Range
        With Worksheets("Compensation Eval - Template")
            Set product1 = .Range("J4")
            With .Range("A12:I350")
                .AutoFilter Field:=2, Criteria1:=product1
            End With
        End With
    End If
End Sub

The issue was solved by moving the code from a module to the worksheet.

Solution

Try using this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 2 And Target.Column = 3 Then
        Dim product1 as Range, product2 As Range
        With Worksheets("Compensation Eval - Template")
            Set product1 = .Range("B8")
            Set product2 = .Range("L10")
            With .Range("A12:I350")
                .AutoFilter Field:=2, Criteria1:=product1, Operator:=xlOr, Criteria2:=product2
            End With
        End With
    End If
End Sub

Note: Your comment "it does not seem to work" makes it a little tricky to diagnose, but in case you're not aware, this code needs to be pasted into the worksheet object in order to fire when a change is made to that worksheet.