Retrieve or extract cell formatting information using VBA

I would like to know whether there is an efficient way of retrieving
all the cell formatting information in one go and represent it as one
string? There is always the hard way of walking through the entire cell
object model and retrieve property by property but would prefer
something more efficient? E.g. reading the content of the format
painter or so. If the entire cell format can be represented as one
string, is there also a way to apply this formatting string to the cell
again?

Thanks in advance,
Reto

"There is always the hard way of walking through the entire cell
object model and retrieve property by property but would prefer
something more efficient?"

AFAIK, there's no other way to do it. There is no one "property" format that
you could save into a variable. There are many format properties:
numberformat, horizontal and vertical alignment, merge cells, wrap text, all
of the font characteristics (name, size, bold or italic or underlined or
strikethough etc), interior color index, 6 different borders, conditional
formatting, etc, etc, etc.

If you write the code to retrieve those settings and create such a string,
then the only way to apply those settings to a cell would be with more code
that parses that string and applies the settings.

If you want to quickly reapply a group of formats, I would copy the format to
another cell to be used as a sort of "buffer" (Edit/Copy, then Edit/Paste
Special/Formats). Then to reuse the format, you would copy that buffer cell
and paste its format wherever you want. This cell would have to be protected
from changes by the user, of course.


On 1 Oct 2005 17:13:23 -0700, "rlaemmler@hotmail.com" <rlaemmler@gmail.com>
wrote:

>I would like to know whether there is an efficient way of retrieving
>all the cell formatting information in one go and represent it as one
>string? There is always the hard way of walking through the entire cell
>object model and retrieve property by property but would prefer
>something more efficient? E.g. reading the content of the format
>painter or so. If the entire cell format can be represented as one
>string, is there also a way to apply this formatting string to the cell
>again?
>
>Thanks in advance,
>Reto

Too bad nothing more efficient exists. Oh well, than I will get started
for the hard way. Thanks anyway for your prompt reply.