Execute Macro Based on Cell Change -> without using Enter key

[mosesthetank]

The below macro creates a filter based on the value in A2. I want it to execute every time A2 changes. A2 is a cell reference to another sheet (pivot table). The only way the filter currently updates is if I press Enter in A2. I have tried writing this several different ways but they all require pressing Enter.

Can the below code be be written so as to refresh the filter as A2 changes without using Enter?

[FONT=Arial]Private Sub Worksheet_Change(ByVal Target As Range)[/FONT][FONT=Arial]Dim KeyCells As Range[/FONT]
[FONT=Arial]Set KeyCells = Range("A2")[/FONT]
[FONT=Arial]
[/FONT]
[FONT=Arial]If Not Application.Intersect(<wbr>KeyCells, Range(Target.Address)) _[/FONT]
[FONT=Arial]           Is Nothing Then[/FONT]
[FONT=Arial]
[/FONT]
[FONT=Arial]Set Target = Target.Cells(1, 1)[/FONT]
[FONT=Arial]If Not Intersect(Target, Range("A2")) Is Nothing Then[/FONT]
[FONT=Arial]Range("B2").CurrentRegion.<wbr>AutoFilter field:=2, Criteria1:=Target.Value[/FONT]
[FONT=Arial]End If[/FONT]
[FONT=Arial]
[/FONT]
[FONT=Arial]
[/FONT]
[FONT=Arial]End If[/FONT]
[FONT=Arial]
[/FONT]
[FONT=Arial]End Sub[/FONT]

 

[offthelip]

put the code into the worksheet calculate event instead of (or as well as) the worksheet change event

 

[mosesthetank]

I change it to Worksheet_Calculate and got a compile error:

Procedure declaration does not match description of event or procedure having the same name.

Any guidance would be appreciated.

 

[offthelip]

The worksheet calculate event does not include the the "Target" parameter so you either need to detect whether A2 has changed by keeping a record of it or just recalculate the filter every time using code like this:

Private Sub Worksheet_Calculate()

Range("B2").CurrentRegion.AutoFilter field:=2, Criteria1:=Cells(1, 1).Value


End Sub

 

[mosesthetank]

Thank this worked.