paste and highlight the cells of range

[catchchatur]

Experts,

I have a range to copy and paste. The range to copy varies with a
variable value. While defining the target range, I just give reference
to first cell of the column where it has to start pasting.

For example, If I want to copy values in cells A1:A10 in the the
column B, I use

rng_source.Copy 'range
A1:A10, it varies with input given
rng_target.PasteSpecial Paste:=xlPasteValues 'range points at
first cell of target, here it is B1
rng__target.Interior.ColorIndex = 5 'setting the color for the cells
copied

These steps just highlight the first cell of target and not the entire
range. I want the whole range from B1 to B10 to be highlighted. Is
there any way to do it?

Harshit

 

[JMay]

This worked for me... (In a standars module)..

Sub Foo()
Set source_rng = Range("A1:A10")
Set Target_rng = Range("B1:B10")
source_rng.Copy
Target_rng.Select
ActiveSheet.Paste Destination:=Target_rng
Range("a1").Select
Application.CutCopyMode = False
End Sub

 

[catchchatur]

JMay,

I realize I should have been more clear. The problem is, here are you
are defining target range as B1:B10.

How will you do it, if you define target range just as "B1"

consider this

Sub Foo()
Set source_rng = Range("A1:A10")
Set Target_rng = Range("B1") 'ending row and cell is not specified
here, it varies
source_rng.Copy
Target_rng.Select
ActiveSheet.Paste Destination:=Target_rng
Range("a1").Select
Application.CutCopyMode = False
End Sub

Here too, your code will work. It will copy the contents. But how to
format (highlight) the cells in which values are pasted?

One way I am doing is

rng_source.Interior.ColorIndex = 24
rng_source.Copy

rng_target.PasteSpecial Paste:=xlPasteValues
rng_target.PasteSpecial Paste:=xlPasteFormats

This code formats the source cells and then I am copying values and
formats individually. Is there any way of doing this without making
any changes in source file? If the range for target is fixed, it is
easy to do, but if range for target is not fixed, I am hitting
walls ..

Can you help me now?

 

[JMay]

How 'bout..

Sub Foo()
Set source_rng = Range("A1:A10")
Set target_rng = Range("B1")
source_rng.Copy
Set target_rng = target_rng.Resize(source_rng.Rows.Count, 1)
ActiveSheet.Paste Destination:=target_rng
Range("a1").Select
Application.CutCopyMode = False
End Sub

 

[Dave Peterson]

To change the colorindex

rng_target.Interior.ColorIndex = 5

becomes

with Source_rng
rng_target.resize(.rows.count,.columns.count).interior.colorindex = 5
end with