Make dynamic advanced filter trigger on formula value update

[szakharov7723]

So currently I have an advanced filter that triggers on value input. However when I try to make criteria cell formula based it ignores changes. unless I click on cell and hit enter
Here is my advanced filter

Private Sub Worksheet_Change(ByVal Target As Range)    If Not Intersect(Target, Range("A5:S7")) Is Nothing Then
        On Error Resume Next
        ActiveSheet.ShowAllData
        Range("A9").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A4").CurrentRegion
    End If
End Sub

Is it possible to make triggered on formula value changes ?

 

[szakharov7723]

Thank you a lot. Looks like it is working.
So generally speaking, when I add new range in

[COLOR=#333333] If Not Intersect[/COLOR]

I can use this range as both value and formula for other ranges in Intersect ?

 

[Fluff]

The formula will not trigger the change event, so you have to add the cells that the formula looks at.
So as the formula in E5 is looking at P2, you need to add P2 to the intersect range

 

[szakharov7723]

yep, got it
thank you !

 

[Fluff]

Glad we could help & thanks for the feedback

 

[DanteAmor]

One option is to put in the range the cells that make up the formula, that is, if the cell A5 has the formula =A3 and really the value you are changing is A3, then you should have:
If Not Intersect (Target, Range ("A3"))

That I mentioned from post # 2