Background Color

S

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 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
 
R

Ron de Bruin

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

See the VBA help for pastespecial
 
S

smandula

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.
 
R

Ron de Bruin

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

smandula

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
 
R

Ron de Bruin

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

Can you explain

Which range do you want to copy and where
 
S

smandula

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

The above starts to copy, a cell at a time across columns from Sheet 2
This value is contained in ' rCell ' and pasted to sheet 1 starting at
' 0 '
For instance number 3 in Cell D2 would line up with column D on Sheet
1
I would like to simultaneously ( if possible color the background of
Cell D2).
Only seven numbers out of 47 numbers per row are displayed. Some will
be
colored when copied from Sheet 2, if Sheet 2 colors one or more cells.
 
R

Ron de Bruin

Sorry, I not understand you to give a good answer

Maybe a English speaking regular can help you
 
D

Dave Peterson

You've got another suggestion at your other post.
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

separate data with extra column 1
use border for IF test 1
Transfer background color when necessary 2
cell selection, merging..... 1
merge cells 6
VBA Coding Help for Beginner 0
Swap 2 ranges 3
Slow Code 9

Top