Random sheets in Excel 2007 Workbooks slowing down?

[Thebatfink]

Hi,

Its a bit of a broad question but I'm having issues with some of my workbooks.

They start fine (I have a template new books are created from). They are used every day with information added to them over the weeks and saved only a daily basis etc.

There are some formulas on them (using a custom function which looks up in an Access database) but the sheets are only 70 rows large..

My first impression was these formulas using the lookup function was slowing things down but it doesn't look like it is.

When sideways scrolling for example the sheet moves really slow and you can see the newly exposed cells repainting on the screen. If you try and copy and paste a few lines, the whole book just freezes and effectively crashes!

Other pages aren't affected. And they suddenly start going slowly seeming randomly.. I'm getting to my wits end with them. I don't suppose anyone has any ideas why this could be happening?

One thing of note.. I have just opening the offending workbook in excel 2003 (albiet in read only mode) and the same sheet which is rediculously slow in 2007 is working fine in 2003?? When it opens it says a few things during the file converting process -

"Uninitialized ActiveX controls will not be opened in this version of Excel"
"Some cells have more conditional formats than are allowed in the version of Excel. Only the first three conditions will be displayed"
"Some cells have overlappying conditional formatting ranges. This version of Excel will not evaluate all the conditional formatting rules on the overlappying cells."

Could the conditional formatting be an issue? If so, why only later in the workbooks life?

Any help would be really appreciated. I just don't understand what to do and I'm on the verge of scraping the whole idea of Access lookups and it would be such a shame as they have really increased productivity.

Thanks
Batfink!

 

[Thebatfink]

Ok, I have found the problem!!!

For some reason which I never knew excel did, copying and pasting cells is making the conditional format rules copy and paste. Whilst this would be ok I guess, its also adding them to whatever was already existing in the cell in the first place. Im ending up with cells with 10's and 10's of conditional formats..

In 2003 I used to right click drag and select copy formats to copy conditionals, how come 2007 is copying them with the normal copy and paste, and if possible, how can I stop it??

Thanks!