PDA

View Full Version : Worksheet Change Event



peacenik
03-18-2007, 11:26 PM
I am using the Worksheet Change event to control behaviour in the sheet, but I don't want changes done via a macro to use these rules. Is there some way to exclude changes done within a macro from this code. Here is some sample code.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Interior.ColorIndex <> 0 Then Target.Interior.ColorIndex = 0
End Sub



Thanks for taking the trouble to read this.

JimmyTheHand
03-19-2007, 12:53 AM
Try this

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Interior.ColorIndex <> 0 Then Target.Interior.ColorIndex = 0
Application.EnableEvents = True
End Sub

Jimmy

mdmackillop
03-19-2007, 01:44 AM
This
Application.EnableEvents = False
'Your code
Application.EnableEvents = True
has to go into all the other workbook macros as well as the Worksheet_Change one.
Jimmy's will stop the change code looping; in the other code it will prevent the Change code from running.