I have a worksheet that includes conditional formatting and a button that
executes a macro to clear and update a few of the cells. These cells are not
the ones with the conditional formatting. My problem/issue is that when I
execute the macro the conditional formatted cells are reset to their
pre-conditional format condition. Immediately afterwards I can enter data
into any unrelated cell and the conditional formatting takes effect.
Pressing F9 (recalculate) also would make the conditional formatting
effective. I’ve tried adding the 'application.calculate' code to the end of
the macro but it has no impact.

What am I doing, or not doing to cause the conditional formatting to act
this way?

Thanks
- Pat

Check that you have not got some code being triggered by an event from the
macro you are running. This does all sorts of funny things and not
necessarily what you might expect. The following lines of code
disables/enables events being triggered. It can be inserted as the first and
last lines of a procedure to suppress other events running. But don't forget
to turn it back on in the last line or other events that you do want to run
later will not run.

Application.EnableEvents = False 'To disable
Application.EnableEvents = True 'To enable again
regards,

OssieMac

"Dreiding" wrote:

> I have a worksheet that includes conditional formatting and a button that
> executes a macro to clear and update a few of the cells. These cells are not
> the ones with the conditional formatting. My problem/issue is that when I
> execute the macro the conditional formatted cells are reset to their
> pre-conditional format condition. Immediately afterwards I can enter data
> into any unrelated cell and the conditional formatting takes effect.
> Pressing F9 (recalculate) also would make the conditional formatting
> effective. I’ve tried adding the 'application.calculate' code to the end of
> the macro but it has no impact.
>
> What am I doing, or not doing to cause the conditional formatting to act
> this way?
>
> Thanks
> - Pat
>

I tried it with "Application.EnableEvents = True" after macro execution and
found no difference. However, I did find a solution, but not an explanation.
Instead of using a button created via the Control Toobox, I created a button
using the Forms button object. I also added a button to an existing toolbar
and that also work. A lesson learned from this is that interrupts are
controlled differently when using the Control Toolbox objects.

- Pat

"OssieMac" wrote:

> Check that you have not got some code being triggered by an event from the
> macro you are running. This does all sorts of funny things and not
> necessarily what you might expect. The following lines of code
> disables/enables events being triggered. It can be inserted as the first and
> last lines of a procedure to suppress other events running. But don't forget
> to turn it back on in the last line or other events that you do want to run
> later will not run.
>
> Application.EnableEvents = False 'To disable
> Application.EnableEvents = True 'To enable again
> regards,
>
> OssieMac
>
> "Dreiding" wrote:
>
> > I have a worksheet that includes conditional formatting and a button that
> > executes a macro to clear and update a few of the cells. These cells are not
> > the ones with the conditional formatting. My problem/issue is that when I
> > execute the macro the conditional formatted cells are reset to their
> > pre-conditional format condition. Immediately afterwards I can enter data
> > into any unrelated cell and the conditional formatting takes effect.
> > Pressing F9 (recalculate) also would make the conditional formatting
> > effective. I’ve tried adding the 'application.calculate' code to the end of
> > the macro but it has no impact.
> >
> > What am I doing, or not doing to cause the conditional formatting to act
> > this way?
> >
> > Thanks
> > - Pat
> >

You would not find any difference setting "Application.EnableEvents = True"
at the end of the procedure unless you set it to false at the start of the
procedure. It is true by default and you need to set it to false at the start
of a procedure to prevent any other events being called inadvertantly and
then setting it to true on the last line of the procedure puts it back to its
default condition.

An easy way to check whether events are being called inadvertantly is during
development put the following line of code as the first line of every event
procedure.

Msgbox "This sub name". Replace this sub name with the name of the sub where
you have located it. If you see the message while another event is being run
then you will know that it has been triggered. After testing I simply comment
it out because it is easy to take the comment out if I do any modifications
later.

Regards,

OssieMac

"Dreiding" wrote: