Saving a Range and putting it back again

[Jean Ryckebosch]

Hi all,

Here's what I'm trying to do. Suppose you have a range of text (eg with a
yellow background color). I want to copy this Range, make the background
color red, then put the old range (that includes the yellow background)
back.

I would guess this would look something like this :

Dim prevRange As Range 'this would be a global variable

'Save the range
Set prevRange = myRange.Duplicate
'Change the range
myRange.Shading.BackgroundPatternColor = myColor 'myColor can be any color

'and later on, restore the range to its original state

myRange.FormattedText = prevRange.FormattedText

I always get an error on this last statement saying that it can't copy
between these two ranges. How can I save a Range (and all the formatting
that goes along with it), make some changes to the existing range, and put
the saved range back?
I don't want to use the clipboard (so no Copy and Paste)

Kind regards,

Jean

 

[Helemut Weber]

Hi Jolanda,
hmm...?
May I ask what it would be good for,
to change something and to undo the changes
afterwards in a macro?
If you got a pretty fast machine, you may not see
anything at all.