Excel Error :- "Too Many Cell Formats"...is there a way of finding

[FoS605]

Hi all,

have seen numerous threads about the "Too Many Cell Format" error messages,
and the useful suggestions on how to deal with it.
However is there a way of finding out how many cell formats there are in a
work book?

That way I could do some preventative work prior to getting to the "4000"
limit.

Grateful for nay replies!

 

[Kevin B]

One thing you can do is do not pre-format rows and columns you're not using.

Another would be to add border lines from the FORMAT/CELLS dialog box and
not the command button on the toolbar that assigns lines on all 4 sides.
Let's say you want to format some adjacent cells with lines on the left,
right, top and bottom.
When you apply that to cell A1, when you format cell B1 it won't need a
border on the left as the right border of cell A1 produces that same effect.
Additionally, if you extended the same formats to row 2, cells A2 and B2
won't need top border lines as the bottom border line of cells A1 and B1
has been applied.

Hope this helps.

 

[FoS605]

Hi Kevin,

Thank you for taking the time to reply with your suggestions.
Although I still want to know if there is a way of actually finding out the
number of formats being used in a work book. There must be someway of
finding the number otherwise how does excel know when to display the error
dialogue box?


Cheers.

 

[Kevin B]

Off the top of my head I don't know of an easy way to count all the formats
in a workbook. If you click FILE/PROPERTIES you'll see that it's not one of
the statistics that is available.

Aside from some VBA code to evaluate each cell for the different types of
formatting, I don't know of a way to get that information.

So, I won't say there isn't a way, but it doesn't look as if there's an easy
way of gathering that particular piece of information.

 

[FoS605]

Kevin,
I am hoping someone with VBA experience may know how to establish the number.
Much appreciate your helpful suggestions. Thanks