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

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

On Apr 27, 4:59 pm, "Ron de Bruin" wrote:
Instead of the Value property you can use pasteSpecial.
paste the Formats first and then the values

See the VBA help for pastespecial

I need more help in executing your suggestion.

Can you tell me what you exactly want to do?
When reading your code it is not clear to me

--

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


"smandula" wrote in message ups.com...
On Apr 27, 4:59 pm, "Ron de Bruin" wrote:
Instead of the Value property you can use pasteSpecial.
paste the Formats first and then the values

See the VBA help for pastespecial

I need more help in executing your suggestion.

Thanks for your come back.
I don't know if it's possible using pasteSpecial.

Sheet 2 has variable columns, in this example case 9 columns
by 150 rows, all rows and columns are filled with data on
Sheet2.

However, on Sheet 1 where this data is being copied to,the rows
and columns are different in size. Namely, 48 columns by 150 rows.
This part of the macro works great.

I would like to color some cells of the 9 columns per row bases
of Sheet 2 and be able to copy these colored items on Sheet 1,
at the same time the individual cell value is being copied.

I value your opinion, in that if it's possible within the given macro,
or if a separate macro is needed.

With Thanks

you offset with a cell value ?
..Offset(0, rCell.Value).

Can you explain

Which range do you want to copy and where

--

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


"smandula" wrote in message ups.com...
Thanks for your come back.
I don't know if it's possible using pasteSpecial.

Sheet 2 has variable columns, in this example case 9 columns
by 150 rows, all rows and columns are filled with data on
Sheet2.

However, on Sheet 1 where this data is being copied to,the rows
and columns are different in size. Namely, 48 columns by 150 rows.
This part of the macro works great.

I would like to color some cells of the 9 columns per row bases
of Sheet 2 and be able to copy these colored items on Sheet 1,
at the same time the individual cell value is being copied.

I value your opinion, in that if it's possible within the given macro,
or if a separate macro is needed.

With Thanks

You've got another suggestion at your other post.

smandula wrote:

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

--

Dave Peterson

Thanks everyone for listening