Excel locking up when displaying a preview of "Paste Special" into a range with Cond. formatting

[FrankBa]

Excel 2010, 32 bit OS, dual core, 4G RAM

One of our team has a fairly large spdsht that has one page with a large number of conditional formats applied to it (the range containing the formats is currently 66 columns and 1655 rows, with a conditional format for every column). His process is to take data extracted from our Oracle db and do a "Paste Special" (Values only, no formatting) with the data. He copies the data, then selects the cell to start his paste and opens the "Paste" menu on the ribbon bar. When he mouses over the appropriate icon, Excel locks up for an extended period of time (often HOURS) and occasionally crashes. I know that Excel only redraws conditional formatting for displayed cells, but I'm wondering if anyone here knows if the previewing that Excel does when you mouse over the icons in the Paste menu is trying to redraw everything, even if it's not displayed???

Also, in light of trying to resolve this situation. The conditional formatting rules being used all include "Cell Value < 0" and "Cell Value > 0" rules for each column. Is there a performance benefit to having one rule for all the columns, rather than multiple rules with identical conditions/formats?

I know that this kind of pushes the normal rule of one subject per thread, but if question #1 is the issue, then question #2 could be part of the solution.

 

[Technowolf]

Wow. Yeah that much conditional formatting would definitely kill excel. Excel basically has to process 109,230 formulas and colors with every action. You might be able to get around the lockup with a macro. Something like:
(run this while on the sheet with all the conditional formatting)

Sub PasteData()
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    ActiveSheet.Cells.Copy
    Sheets.Add
   ' ^ This adds a new sheet to paste to. If you have a specific sheet you want to paste to, you can change this to:
   ' Sheets("YourSheetName").Select
    ActiveSheet.Range("A1").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Range("A1").Select
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

That should theoretically be a lot faster, but I also don't have a giant spreadsheet full of conditional formatting to test it on.

 

[FrankBa]

Wow. Yeah that much conditional formatting would definitely kill excel. Excel basically has to process 109,230 formulas and colors with every action. You might be able to get around the lockup with a macro. Something like:
(run this while on the sheet with all the conditional formatting)

Sub PasteData()
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    ActiveSheet.Cells.Copy
    Sheets.Add
   ' ^ This adds a new sheet to paste to. If you have a specific sheet you want to paste to, you can change this to:
   ' Sheets("YourSheetName").Select
    ActiveSheet.Range("A1").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Range("A1").Select
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

That should theoretically be a lot faster, but I also don't have a giant spreadsheet full of conditional formatting to test it on.

I thought about that as well. We turned off Live Preview, which seemed to help it from locking up, but we're still not where we need to be. Then I found out that the cells he has the conditional formatting in ALL have SUMIFs in them that use full columns as parameters. Time for some education into using Tables linked to queries...

Thanks for the time and input.