Format Paste from nonmerged cell to merged cell

[Annette]

Is there a method to paste from a nonmerged cell to a merged cell the color
of the originator? I am able to use this macro to copy the number, but I
need the originating color from time to time and wondered if it that could
be included in the macro.

Sub CopyProductCells
ActiveCell.Select
Selection.Copy
ActiveSheet.Previous.Select

ActiveCell.Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

End Sub

Annette

 

[royUK]

Most people would recommend that you avoid using Merged Cell

--
royU

Hope that helps, RoyUK
For tips & examples visit my 'web site
' (http://www.excel-it.com

 

[Peter T]

You could store the .Interior.ColorIndex property of the merged area to a
variable and apply same to the destination range. You could do that in
tandem with your paste special.

Regards,
Peter T

 

[Annette]

Peter, can you tell me how I would do this?

Thanks!

 

[Peter T]

The following is based as best I can on what you posted earlier. Not sure
it'll work as required but see how you get on.

Generally it's not usually necessary to select something, though with your
'ActiveSheet.Previous.Select' in this case looks as if it is.

Sub test()
Dim clrIdx As Long
Dim rngSource As Range
Dim rngDest As Range

Set rngSource = Selection
rngSource.Copy
clrIdx = rngSource(1).Interior.ColorIndex

ActiveSheet.Previous.Select
Set rngDest = Selection

rngDest.PasteSpecial Paste:=xlFormulas
rngDest.Interior.ColorIndex = clrIdx

End Sub

Regards,
Peter T