Conditional Formatting - 2003 to 2007 errors

[Richard MG]

I have a large workbook built in 2003 that has a considerable amount of
Conditional
Formatting (CF) that works fine in 2003. When I run it in 2007 Compatibility
mode I find that some, not all, of the CFs don't work first time. It goes
like this:

Cell has CF to change its colour if a second cell changes to TRUE. This is
triggered by the User ticking a control. This is repeated many times down the
page with each event independent of the others.

In 2007 when the control box is ticked sometimes it works fine but on other
occasions the box accepts the tick but nothing happens. If a number is
entered into the offending cell it changes colour, then if the control is
unticked and ticked again it works fine and continues to do so if the control
is ticked and unticked . If this is done for event 1 it seems to remedy event
2 and onwards. Whether the event works or not seems to be fairly random
initially but entering the number once seems to sort out the whole sheet.

This seems like a bug, but how do I remedy it?

 

[Jan Karel Pieterse]

Hi Richard,

In 2007 when the control box is ticked sometimes it works fine but on other
occasions the box accepts the tick but nothing happens.

I strongly suspect this is due to the combination of using many controls and
CF.
How many checkboxes are there?

What happens if you change the type of checkbox (from control toolbox controls
to forms toolbar controls)?

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Richard MG]

About 120, but not all have CF. This is then repeated on 4 more sheets.

I got some guidance on another forum which suggested that the challenge
might be the "Stop If True" box. This seemed to be linked to a CF with more
than one condition, which none of mine have. However, I have just removed the
tick and 2007 now seems to work. I then saved the workbook and was warned
that this feature wasn't supported in 2003. I ignored the warning and saved
it. It still seems to work OK in 2003.

Can you tell me what is going on here?

 

[Jan Karel Pieterse]

Hi Richard,

However, I have just removed the
tick and 2007 now seems to work. I then saved the workbook and was warned
that this feature wasn't supported in 2003. I ignored the warning and saved
it. It still seems to work OK in 2003.

Can you tell me what is going on here?

No, not really. It should work without that box checked if you ask me.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Richard MG]

For others reading this - It seems to be something to do with the
registration of the page, if the page registers! My work-a-round has been to
copy all the offending tabs, rename the old one ".... old", change the name
of the new one to the original name by removing the "(2)" and then deleting
the old one. Find and replace all text references to any link to the old
tab(s) that have been replaced. It worked for me whether saved as an .xlsm or
..xls. (remember that the workbook originated in 2003 so there are no
challenges with bits that are 2007 only)