Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
paste and highlight the cells of range
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? Har**** |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
paste and highlight the cells of range
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 " wrote: 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? Har**** |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
paste and highlight the cells of range
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? On Jun 27, 2:18 pm, JMay wrote: 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 " wrote: 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? Har****- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
paste and highlight the cells of range
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 " wrote: 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? On Jun 27, 2:18 pm, JMay wrote: 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 " wrote: 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? Har****- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
paste and highlight the cells of range
To change the colorindex
rng_target.Interior.ColorIndex = 5 becomes with Source_rng rng_target.resize(.rows.count,.columns.count).inte rior.colorindex = 5 end with " wrote: 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? On Jun 27, 2:18 pm, JMay wrote: 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 " wrote: 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? Har****- Hide quoted text - - Show quoted text - -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy/paste won't refer to the appropriate range of cells | Excel Discussion (Misc queries) | |||
How do I copy the contents of a range of text cells and paste into one cell? | Excel Discussion (Misc queries) | |||
want highlight when all cells in range are equal | Excel Worksheet Functions | |||
Highlight cells with ctrl-click but only un-highlight one cell | Excel Discussion (Misc queries) | |||
How to paste INDIRECT function to range of cells? | Excel Worksheet Functions |