Losing conditional formatting when saving

[Guest]

I have created quite an extensive excel file with a significant amount of
conditional formatting. I have reached the point where if I save now the
conditional formatting of the last few rows is not saved. Is there a way
around this or is there a limit to how much conditional formatting you can
use?

 

[Dave Peterson]

I've never noticed xl dropping any CF, but I don't use it a whole bunch.

But if it drops it for you, maybe you could reapply it in the
workbook_open/auto_open code each time the workbook opens.

You may want to post some specifics.

How many rows. How you used Format|conditional formatting? (Cell by cell or
column by column--I would link that if you could format the entire column, it
would be less work for excel (but that's a guess).)

 

[Guest]

To be more precise on this problem...

I have all the columns that Excel allows for inlcuded in the model and some
500 rows. The columns do indeed all have the same CF, which is quite
extensive in itself. The CF per row is organised differently. Each time there
would be a row containing all the CF followed by a row with no CF.

As said, now that I add more and more rows, the model loses its conditional
formating memory capacity (or so it seems). Right now the CF is done on a per
cell basis.

Does this provide you with more information to help solve the problem..?

 

[Dave Peterson]

If excel drops those CFs, I don't think I'll be able to solve the problem.

My suggestion is more of a workaround.

If you know that excel drops stuff, add it back when you open the workbook.

 

[David McRitchie]

Hi Rogier,
If you are losing Conditional Formatting then perhaps you are
pasting into your cells. If you paste the formats you will lose
C.F. or specifically change them to the format of the pasted in
cells. Paste only values.

However you do say that you are inserting rows. When you
create your C.F. are you formatting the entire sheet or just
individual cells. Suggest formatting the entire sheet, or columns
you will be using not just the current cells.
http://www.mvps.org/dmcritchie/excel/condfmt.htm