Autofilter stops Change Event

[Risky Dave]

Hi,

I have a worksheet change event set to fire under certain circumstances, but
applying Autofilter to my data columns stops it from happening.

Can anyone explain why?

TIA

Dave

 

[Gord Dibben]

Not without seeing your code.


Gord Dibben MS Excel MVP

 

[Risky Dave]

Gord,

Thanks. Here it is:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.EnableEvents = False

If Not Application.Intersect(Target, Range("m:m,o,t:u,z:aa")) Is Nothing
Then
Select Case Target.Column
Case Is = 15, 16 'update gross score
Range("q" & Target.Row).Value =
Sheets("tables").Range("b9").Offset(-Range("o" & Target.Row), Range("p" &
Target.Row))
Case Is = 20, 21 ' update net score
Range("v" & Target.Row).Value =
Sheets("tables").Range("b9").Offset(-Range("t" & Target.Row), Range("u" &
Target.Row))
Case Is = 26, 27 'update target score
Range("ab" & Target.Row).Value =
Sheets("tables").Range("b9").Offset(-Range("z" & Target.Row), Range("aa" &
Target.Row))
Case Is = 13 ' detect close/open a risk
' format the line after closure
Select Case Range("m" & Target.Row)
Case Is = "Closed" 'blank out closed risk
Range("A" & Target.Row & ":AC" & Target.Row).Select
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Range("O" & Target.Row & ":Q" & Target.Row & ",T" &
Target.Row & ":V" & Target.Row & ",Z" & Target.Row & ":ab" &
Target.Row).Value = ""
Case Is = "Open" 're-set re-opened risk
Range("a" & Target.Row & ":ac" & Target.Row).Select
With Selection.Interior
.ColorIndex = xlNone
End With
Range("O" & Target.Row & ":Q" & Target.Row & ",T" &
Target.Row & ":V" & Target.Row & ",Z" & Target.Row & ":ab" &
Target.Row).Value = ""
Range("m" & Target.Row).Select
End Select
End Select
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub