Autofilter stops Change Event

  • Thread starter Thread starter Risky Dave
  • Start date Start date
R

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,

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:p,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
 
Back
Top