Stuck in a loop
 

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!

Stuck in a loop
 

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



"CWillis" wrote in message
...
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!

Stuck in a loop
 

Works great! Thanks Chip.

"Chip Pearson" wrote:

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



"CWillis" wrote in message
...
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!