Paste Special, Format Trouble

  • Thread starter Thread starter Corey872
  • Start date Start date
C

Corey872

Greetings all,

I am having trouble with a spreadsheet I am working on. The
spreadsheet is arranged as about 20,000 lines, 16 colums. (which I
gather is not terribly big considering some of the spreadsheets I have
read about) The data is grouped as follows:


SAMP1 ELEMENT 1 X...(+ 13 colums)
ELEMENT 2 Y...
DATE
TIME

SAMP 2 ELEMENT 1 X...
ELEMENT 2 Y...
DATE
TIME

ETC.

Once I have about 13 colums of element concentrations, I go to the end
colum and calculate the average...so far, so good. Next, I would like
to apply conditional formatting so that if the element concentration
is say Average + 10% the cell is colored red and if it is Average -
10% the cell is colored blue. Again, no problem. Lastly, what I do
is select the first four rows (only the ones that contain the
conditional formatting) and choose copy, then highlight the rest of
the spreadsheet and choose Paste Special, Formats. This appears to
work, and puts the conditional formatting on the first two rows of
each group of four, but when I hit "save" the progress indicator goes
to about 90% then the entire program crashes just like someone hit
"close". No error messages of any kind.

If I hit save just before I copy/paste it works fine, and I can
continue to work with the spreadsheet for hours after the copy/paste
as long as I don't hit "save"

I have tried this on two separate computers...one is Win XP with
Office XP, the other is Win NT with Office 97. In each instance,
hitting save crashes the program.

Some things I have tried:

Cut all cells with formulae and paste values only
Try conditional formatting only one column at a time

No luck...program crashes every time I hit save.

Any thoughts on what may be wrong?

Corey
 
I'm sure this isn't what you want to hear, but in my experience, lots of
conditional formatting can be a problem.

If you take a few hundred rows and copy them to a new workbook, and apply your
CF, do you get the same problem?

If no, try copying all the data to a new worksheet in a new workbook and give it
a shot--maybe it's not the CF, but the worksheet itself that's having trouble.

And you may want to clean up your windows temp folder just to see if that
helps. (maybe close all the other applications, too.)

If none of these helped, you may want to take a look at these two sites
(Charles Williams and David McRitchie):

http://www.decisionmodels.com
http://www.mvps.org/dmcritchie/excel/slowresp.htm

They have some tips for working with slow workbooks.

=======
And if all that fails, maybe if your data doesn't change too much/often, you
could just have a macro that applies the formatting for you.
 
Back
Top