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

Not without seeing your code.


Gord Dibben MS Excel MVP

On Tue, 16 Feb 2010 08:05:02 -0800, Risky Dave
wrote:

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

"Gord Dibben" wrote:

Not without seeing your code.


Gord Dibben MS Excel MVP

On Tue, 16 Feb 2010 08:05:02 -0800, Risky Dave
wrote:

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

.