Worksheet change event

[Fwiz]

hi,

just look at the worksheet change event, I'm trying to run autofiler if any value on column G has changed value - I've got some code but cannot get it to work.....

Sub Worksheet_Change(ByVal Target As Range)

'If Target.Row > 2 And Target.Column = 7 Then
If Target.Address = "$G$3:$G$65536" Then
Selection.AutoFilter Field:=7, Criteria1:="=Unpaid", Operator:=xlAnd
Range("A1").Select
End If
End Sub

any suggestions??

 

[Andrew Poulsom]

Instead of:

If Target.Address = "$G$3:$G$65536" Then

try:

If Not Application.Intersect(Target, Range("$G$3:$G$65536")) Is Nothing Then

 

[Fwiz]

I've added this in, it seems to filter on a couple of items, when I change values again the macro doesnt run...... any thoughts?

 

[Andrew Poulsom]

The procedure will fire each time you change a cell in the range G3:G65536.

 

[Fwiz]

it doesnt seem to fire each time i change a new value, i'm expecting the auto filter to filter out the paid items to leave me with the unpaid ones.... ?

 

[Andrew Poulsom]

You can test if it's firing by putting something like:

MsgBox "Hello"

inside the If clause.

 

[Jonmo1]

How are the values in $G$3:$G$65536 changed??
Via Manual data entry, or Via Formula??

 

[Fwiz]

via cell dropdown - paid or unpaid.....

 

[Jonmo1]

It's probably related to the use of Selection on the Autofilter line...
Selection may not be what you think it is...

I assume row 2 is headers,
Try

Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("$G$3:$G$65536")) Is Nothing Then
    Range("G3").CurrentRegion.AutoFilter Field:=7, Criteria1:="=Unpaid", Operator:=xlAnd
End If
End Sub