For a special project I need to be able to copy the contents of one cell to
another *including* all formatting. Sometimes I cannot help but to overwrite
a cell I need later on. The tricky part comes when I have to recall the lost
formatting of that overwritten cell. I basically don't have, or want,
permission to rework any sheets that this little 'machine' is going to be
attached to, nor do I want to put such a burden on its users.

For instance, I can get .Interior and put it in an object variable like
this:

Dim interiorTemp as Interior

Set interiorTemp = rngTest(x, y).Interior

Now interiorTemp has all parameters for font, pattern and their color.
Nifty. But I cannot find *any* simple way to do the reverse:

Set rngTest(x, y).Interior = interiorTemp (Error 13, types don't
match)

The help in my version of Excel doesn't specify this but someone pointed out
to me these objects are read-only. I find that unbelievable, but fact is, I
cannot restore.

Some forum members (not this forum) told me to swap the styles. But the
cells could have any additional formatting that is not stored in a Style.

I could write a lot of code and restore the members of the saved objects
one-by-one. If I didn't have to swap I could use rngsource.Copy
rngdestination, which seems to do everything I need, until I need to swap
contents of two cells.

So, the simple question is: How do I put saved formatting into a cell? Is it
possible?

Ava.

I believe that you would have to restore each property (Color/ColorIndex,
Pattern, PatternColor/PatternColorIndex) of the Interior object
individually; e.g.:

rngTest(x, y).Interior.Color = interiorTemp.Color

etc.

Note that this will not help with the formatting of the cell contents (Bold,
Font, Color/ColorIndex, etc.).

--

Vasant


"Ava" <dontspam@comeon wrote in message
...
For a special project I need to be able to copy the contents of one cell
to another *including* all formatting. Sometimes I cannot help but to
overwrite a cell I need later on. The tricky part comes when I have to
recall the lost formatting of that overwritten cell. I basically don't
have, or want, permission to rework any sheets that this little 'machine'
is going to be attached to, nor do I want to put such a burden on its
users.

For instance, I can get .Interior and put it in an object variable like
this:

Dim interiorTemp as Interior

Set interiorTemp = rngTest(x, y).Interior

Now interiorTemp has all parameters for font, pattern and their color.
Nifty. But I cannot find *any* simple way to do the reverse:

Set rngTest(x, y).Interior = interiorTemp (Error 13, types don't
match)

The help in my version of Excel doesn't specify this but someone pointed
out to me these objects are read-only. I find that unbelievable, but fact
is, I cannot restore.

Some forum members (not this forum) told me to swap the styles. But the
cells could have any additional formatting that is not stored in a Style.

I could write a lot of code and restore the members of the saved objects
one-by-one. If I didn't have to swap I could use rngsource.Copy
rngdestination, which seems to do everything I need, until I need to swap
contents of two cells.

So, the simple question is: How do I put saved formatting into a cell? Is
it possible?

Ava.

Ava,
What about a linked picture ?

NickHK

"Ava" <dontspam@comeon wrote in message
...
For a special project I need to be able to copy the contents of one cell
to
another *including* all formatting. Sometimes I cannot help but to
overwrite
a cell I need later on. The tricky part comes when I have to recall the
lost
formatting of that overwritten cell. I basically don't have, or want,
permission to rework any sheets that this little 'machine' is going to be
attached to, nor do I want to put such a burden on its users.

For instance, I can get .Interior and put it in an object variable like
this:

Dim interiorTemp as Interior

Set interiorTemp = rngTest(x, y).Interior

Now interiorTemp has all parameters for font, pattern and their color.
Nifty. But I cannot find *any* simple way to do the reverse:

Set rngTest(x, y).Interior = interiorTemp (Error 13, types don't
match)

The help in my version of Excel doesn't specify this but someone pointed
out
to me these objects are read-only. I find that unbelievable, but fact is,
I
cannot restore.

Some forum members (not this forum) told me to swap the styles. But the
cells could have any additional formatting that is not stored in a Style.

I could write a lot of code and restore the members of the saved objects
one-by-one. If I didn't have to swap I could use rngsource.Copy
rngdestination, which seems to do everything I need, until I need to swap
contents of two cells.

So, the simple question is: How do I put saved formatting into a cell? Is
it
possible?

Ava.

What about a linked picture ?

You mean copying the link? I wasn't thinking in that direction, but that
should be done to I suppose.


NickHK

"Ava" <dontspam@comeon wrote in message
...
For a special project I need to be able to copy the contents of one cell
to
another *including* all formatting. Sometimes I cannot help but to
overwrite
a cell I need later on. The tricky part comes when I have to recall the
lost
formatting of that overwritten cell. I basically don't have, or want,
permission to rework any sheets that this little 'machine' is going to be
attached to, nor do I want to put such a burden on its users.

For instance, I can get .Interior and put it in an object variable like
this:

Dim interiorTemp as Interior

Set interiorTemp = rngTest(x, y).Interior

Now interiorTemp has all parameters for font, pattern and their color.
Nifty. But I cannot find *any* simple way to do the reverse:

Set rngTest(x, y).Interior = interiorTemp (Error 13, types don't
match)

The help in my version of Excel doesn't specify this but someone pointed
out
to me these objects are read-only. I find that unbelievable, but fact is,
I
cannot restore.

Some forum members (not this forum) told me to swap the styles. But the
cells could have any additional formatting that is not stored in a Style.

I could write a lot of code and restore the members of the saved objects
one-by-one. If I didn't have to swap I could use rngsource.Copy
rngdestination, which seems to do everything I need, until I need to swap
contents of two cells.

So, the simple question is: How do I put saved formatting into a cell? Is
it
possible?

Ava.

rngTest(x, y).Interior.Color = interiorTemp.Color

etc.

I'm affraid that is the only way, except hacking with CopyMem, I suppose...


Note that this will not help with the formatting of the cell contents
(Bold, Font, Color/ColorIndex, etc.).

Didn''t get that far yet, but I'll keep an eye on it.

Vasant, is there a reason known to mankind that you know of why these things
are not working in both directions?

Ava.


--

Vasant


"Ava" <dontspam@comeon wrote in message
...
For a special project I need to be able to copy the contents of one cell
to another *including* all formatting. Sometimes I cannot help but to
overwrite a cell I need later on. The tricky part comes when I have to
recall the lost formatting of that overwritten cell. I basically don't
have, or want, permission to rework any sheets that this little 'machine'
is going to be attached to, nor do I want to put such a burden on its
users.

For instance, I can get .Interior and put it in an object variable like
this:

Dim interiorTemp as Interior

Set interiorTemp = rngTest(x, y).Interior

Now interiorTemp has all parameters for font, pattern and their color.
Nifty. But I cannot find *any* simple way to do the reverse:

Set rngTest(x, y).Interior = interiorTemp (Error 13, types don't
match)

The help in my version of Excel doesn't specify this but someone pointed
out to me these objects are read-only. I find that unbelievable, but fact
is, I cannot restore.

Some forum members (not this forum) told me to swap the styles. But the
cells could have any additional formatting that is not stored in a Style.

I could write a lot of code and restore the members of the saved objects
one-by-one. If I didn't have to swap I could use rngsource.Copy
rngdestination, which seems to do everything I need, until I need to swap
contents of two cells.

So, the simple question is: How do I put saved formatting into a cell? Is
it possible?

Ava.

Ava,
Or copy the cell that you wish to preserve somewhere safe, do your stuff,
then copy the original back.

NickHK

"Ava" <dontspam@comeon wrote in message
...
For a special project I need to be able to copy the contents of one cell
to
another *including* all formatting. Sometimes I cannot help but to
overwrite
a cell I need later on. The tricky part comes when I have to recall the
lost
formatting of that overwritten cell. I basically don't have, or want,
permission to rework any sheets that this little 'machine' is going to be
attached to, nor do I want to put such a burden on its users.

For instance, I can get .Interior and put it in an object variable like
this:

Dim interiorTemp as Interior

Set interiorTemp = rngTest(x, y).Interior

Now interiorTemp has all parameters for font, pattern and their color.
Nifty. But I cannot find *any* simple way to do the reverse:

Set rngTest(x, y).Interior = interiorTemp (Error 13, types don't
match)

The help in my version of Excel doesn't specify this but someone pointed
out
to me these objects are read-only. I find that unbelievable, but fact is,
I
cannot restore.

Some forum members (not this forum) told me to swap the styles. But the
cells could have any additional formatting that is not stored in a Style.

I could write a lot of code and restore the members of the saved objects
one-by-one. If I didn't have to swap I could use rngsource.Copy
rngdestination, which seems to do everything I need, until I need to swap
contents of two cells.

So, the simple question is: How do I put saved formatting into a cell? Is
it
possible?

Ava.

Or copy the cell that you wish to preserve somewhere safe, do your stuff,
then copy the original back.

That's only an option for my own sheets I think. I don't know what would
happen with full sheets or protected sheets/workbooks. Won't be pretty, I
suppose.


NickHK

"Ava" <dontspam@comeon wrote in message
...
For a special project I need to be able to copy the contents of one cell
to
another *including* all formatting. Sometimes I cannot help but to
overwrite
a cell I need later on. The tricky part comes when I have to recall the
lost
formatting of that overwritten cell. I basically don't have, or want,
permission to rework any sheets that this little 'machine' is going to be
attached to, nor do I want to put such a burden on its users.

For instance, I can get .Interior and put it in an object variable like
this:

Dim interiorTemp as Interior

Set interiorTemp = rngTest(x, y).Interior

Now interiorTemp has all parameters for font, pattern and their color.
Nifty. But I cannot find *any* simple way to do the reverse:

Set rngTest(x, y).Interior = interiorTemp (Error 13, types don't
match)

The help in my version of Excel doesn't specify this but someone pointed
out
to me these objects are read-only. I find that unbelievable, but fact is,
I
cannot restore.

Some forum members (not this forum) told me to swap the styles. But the
cells could have any additional formatting that is not stored in a Style.

I could write a lot of code and restore the members of the saved objects
one-by-one. If I didn't have to swap I could use rngsource.Copy
rngdestination, which seems to do everything I need, until I need to swap
contents of two cells.

So, the simple question is: How do I put saved formatting into a cell? Is
it
possible?

Ava.

Ava,
Well, if it's protected, wll you be able to achieve the initial copy anyway.
What are you trying to achieve ?

NickHK

"Ava" <dontspam@comeon wrote in message
...
Or copy the cell that you wish to preserve somewhere safe, do your
stuff,
then copy the original back.

That's only an option for my own sheets I think. I don't know what would
happen with full sheets or protected sheets/workbooks. Won't be pretty, I
suppose.


NickHK

"Ava" <dontspam@comeon wrote in message
...
For a special project I need to be able to copy the contents of one
cell
to
another *including* all formatting. Sometimes I cannot help but to
overwrite
a cell I need later on. The tricky part comes when I have to recall the
lost
formatting of that overwritten cell. I basically don't have, or want,
permission to rework any sheets that this little 'machine' is going to
be
attached to, nor do I want to put such a burden on its users.

For instance, I can get .Interior and put it in an object variable like
this:

Dim interiorTemp as Interior

Set interiorTemp = rngTest(x, y).Interior

Now interiorTemp has all parameters for font, pattern and their color.
Nifty. But I cannot find *any* simple way to do the reverse:

Set rngTest(x, y).Interior = interiorTemp (Error 13, types
don't
match)

The help in my version of Excel doesn't specify this but someone
pointed
out
to me these objects are read-only. I find that unbelievable, but fact
is,
I
cannot restore.

Some forum members (not this forum) told me to swap the styles. But the
cells could have any additional formatting that is not stored in a
Style.

I could write a lot of code and restore the members of the saved
objects
one-by-one. If I didn't have to swap I could use rngsource.Copy
rngdestination, which seems to do everything I need, until I need to
swap
contents of two cells.

So, the simple question is: How do I put saved formatting into a cell?
Is
it
possible?

Ava.