Conditional Format limitations

[Picman]

When I try to save my file (Excel 2003) after making some formatting changes
I receive an error message that states:

"Excel could not save all the data and formatting you recently added to
filename.xls.

To return to your workbook without saving click Cancel.
If you want to continue saving changes without repairing the problem, click
OK.â€

What it doesn’t want to save is conditional formatting beyond row 6158. The
whole workbook is only about 12mb, it contains six sheets five of which are
tables of data, and the sixth is a summary page that retrieves data from the
others. The sumary page is approx 8400 rows by 20 columns with every seventh
row containing a conditional format in 14 of the cells across. The
conditional format is simple as well, if the number is less than 0 format the
value to be bold and red, and that’s it. I've tried to remove all of the
other formatting (bold, borders etc.) but nothing seems to work.

I don't understand why Excel is having any difficulties handling this. Got
any ideas? Please.

 

[Shane Devenshire]

Excel 2003 is limited to 4000 styles, so maybe you are bumping up against
that limit. How are you applying the CF - if you apply it to every 20th row
Excel may treat each as a separate format. To avoid that you might be able
to add a condition which determines if the conditional formatting should be
applied to the current row. To help with that we would need to know what
determines which rows are formatted.

 

[Picman]

The CF is on every sixth row but only on selected cells (columns H through
T). These cells are totals of the 4 cells above it. after every sixth row is
a space and then the next series of data begins. 4 rows of data then the
totals with the CF's. i hope that makes sense. See example below.

data 10
data 15
data 10
data 25
total 60 (this is the row with the CF)
(Space)
data 10
data 15
data 10
data 25
total 60 (this is the row with the CF)
etc.