Stuck in a loop

[Guest]

I am using a drop down menu. When an item from the drop down menu is
selected, certain cells are formatted differently. This is done immediately
using:

Private Sub Worksheet_Change(ByVal Target As Range)

Each selection is sent into a seperate sub which first clears any current
formatting and then formats the cells. The problem is that this unformatting
and then formatting continues until I have to hit "esc." Is there a way to
run it only once per selection of the drop down box? Thanks!

 

[Chip Pearson]

You probably need to disable events while your code runs. E.g,

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
'
' your code here
'
Application.EnableEvents = True
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

Works great! Thanks Chip.

You probably need to disable events while your code runs. E.g,

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
'
' your code here
'
Application.EnableEvents = True
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com