Transfer background color when necessary

[smandula]

In this macro, How do you include the background color format?
Color is on Sheet 2, would like to have it carried across to sheet 1
along with data. Any color, but I am using color 4(green).
The Cells being copied to Sheet1 is not always colored, there are
uncolored ones being copied. Sheet 2 has 6 columns with numbers
from 1 to 47. Whereas, Sheet 1 has 47 coloumns waiting to receive
each of the 6 individual numbers. It's a matter of transferring color
for those cells which are colored.
This request has been posted elsewhere, with no solution.
----------------------------------------------
Sub Newer2() 'Final Code
Dim rCell As Range
Dim rCell2 As Range

For Each rCell2 In Sheet2.Range("A2:A150") 'adjust for how many rows
you want to copy
With Sheet1.Range(rCell2.Offset(0, 0).Address) 'Header row has
description row one
.Value = rCell2.Value
For Each rCell In rCell2.Offset(0, 1).Resize(1, 8) 'change all
the columns you need
.Offset(0, rCell.Value).Value = rCell.Value
Next rCell
End With
Next rCell2
End Sub

 

[Dave Peterson]

Maybe you could copy|paste and bring over all the formatting?

..Offset(0, rCell.Value).Value = rCell.Value
becomes
rcell.copy _
destination:=.Offset(0, rCell.Value)

 

[smandula]

Thanks very much, Dave Peterson, for such an elegant
solution. I didn't know where to begin. I was considering
pasteSpecial to no avail.

I works like a charm.

Many Thanks