Instead of the Value property you can use pasteSpecial.
paste the Formats first and then the values

See the VBA help for pastespecial

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"smandula" wrote in message oups.com...
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 3 (green).
----------------------------------------------
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
------------------------------------------------
With Thanks