Stuck in a loop

[CWillis]

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



"CWillis" <[email protected]> wrote in message
news:[email protected]...
>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!

[CWillis]

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" <[email protected]> wrote in message
> news:[email protected]...
> >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!
>
>
>