I want a macro..
that will select a currentregion (starting from A1) and pasting in new sheet

but after pasting.. I want to color the first row and the last row.

The number of row varies..

How canI do it?

Dim rng As Range

ActiveSheet.Range("A1").CurrentRegion.Copy
With Worksheets("Sheet2")
.Activate
.Range("A1").Select
.Paste
Set rng = .Range("A1").CurrentRegion
rng.Cells(1, 1).EntireRow.Interior.ColorIndex = 38
rng(rng.Count).EntireRow.Interior.ColorIndex = 38
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)


"flow23" wrote in message
...
I want a macro..
that will select a currentregion (starting from A1) and pasting in new
sheet

but after pasting.. I want to color the first row and the last row.

The number of row varies..

How canI do it?

thanks
it works

but it colours the entire row (first and last)

and not just the selection

helps

"Bob Phillips" wrote:

Dim rng As Range

ActiveSheet.Range("A1").CurrentRegion.Copy
With Worksheets("Sheet2")
.Activate
.Range("A1").Select
.Paste
Set rng = .Range("A1").CurrentRegion
rng.Cells(1, 1).EntireRow.Interior.ColorIndex = 38
rng(rng.Count).EntireRow.Interior.ColorIndex = 38
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)


"flow23" wrote in message
...
I want a macro..
that will select a currentregion (starting from A1) and pasting in new
sheet

but after pasting.. I want to color the first row and the last row.

The number of row varies..

How canI do it?

Dim rng As Range
ActiveSheet.Range("A1").CurrentRegion.Copy
With Worksheets("Sheet2")
.Activate
.Range("A1").Select
.Paste
Set rng = .Range("A1").CurrentRegion
rng.Cells(1, 1).resize(1,rng.columns.count).Interior.ColorIndex = 38
rng(rng.Count).EntireRow.resize(1,rng.columns.coun t) _
.Interior.ColorIndex = 38
End With

flow23 wrote:

thanks
it works

but it colours the entire row (first and last)

and not just the selection

helps

"Bob Phillips" wrote:

Dim rng As Range

ActiveSheet.Range("A1").CurrentRegion.Copy
With Worksheets("Sheet2")
.Activate
.Range("A1").Select
.Paste
Set rng = .Range("A1").CurrentRegion
rng.Cells(1, 1).EntireRow.Interior.ColorIndex = 38
rng(rng.Count).EntireRow.Interior.ColorIndex = 38
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)


"flow23" wrote in message
...
I want a macro..
that will select a currentregion (starting from A1) and pasting in new
sheet

but after pasting.. I want to color the first row and the last row.

The number of row varies..

How canI do it?





--

Dave Peterson

thanks

But when I paste it in the new sheet at "C20"

It highlights the first row correctly.. but the last row highlight starts
with A20 instead of C20

Also can I add a condition...

If column A... contains word "total".. highlight that enire row?


"Dave Peterson" wrote:

Dim rng As Range
ActiveSheet.Range("A1").CurrentRegion.Copy
With Worksheets("Sheet2")
.Activate
.Range("A1").Select
.Paste
Set rng = .Range("A1").CurrentRegion
rng.Cells(1, 1).resize(1,rng.columns.count).Interior.ColorIndex = 38
rng(rng.Count).EntireRow.resize(1,rng.columns.coun t) _
.Interior.ColorIndex = 38
End With

flow23 wrote:

thanks
it works

but it colours the entire row (first and last)

and not just the selection

helps

"Bob Phillips" wrote:

Dim rng As Range

ActiveSheet.Range("A1").CurrentRegion.Copy
With Worksheets("Sheet2")
.Activate
.Range("A1").Select
.Paste
Set rng = .Range("A1").CurrentRegion
rng.Cells(1, 1).EntireRow.Interior.ColorIndex = 38
rng(rng.Count).EntireRow.Interior.ColorIndex = 38
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)


"flow23" wrote in message
...
I want a macro..
that will select a currentregion (starting from A1) and pasting in new
sheet

but after pasting.. I want to color the first row and the last row.

The number of row varies..

How canI do it?





--

Dave Peterson

How about this:

Option Explicit
Sub testme01()

Dim rng As Range
Dim DestCell As Range

With Worksheets("sheet1")
Set rng = .Range("A1").CurrentRegion
End With

With Worksheets("sheet2")
Set DestCell = .Range("C20")
End With

rng.Copy _
Destination:=DestCell

DestCell.Resize(1, rng.Columns.Count).Interior.ColorIndex = 38
DestCell.Offset(rng.Rows.Count - 1, 0).Resize(1, rng.Columns.Count) _
.Interior.ColorIndex = 38

End Sub

flow23 wrote:

thanks

But when I paste it in the new sheet at "C20"

It highlights the first row correctly.. but the last row highlight starts
with A20 instead of C20

Also can I add a condition...

If column A... contains word "total".. highlight that enire row?

"Dave Peterson" wrote:

Dim rng As Range
ActiveSheet.Range("A1").CurrentRegion.Copy
With Worksheets("Sheet2")
.Activate
.Range("A1").Select
.Paste
Set rng = .Range("A1").CurrentRegion
rng.Cells(1, 1).resize(1,rng.columns.count).Interior.ColorIndex = 38
rng(rng.Count).EntireRow.resize(1,rng.columns.coun t) _
.Interior.ColorIndex = 38
End With

flow23 wrote:

thanks
it works

but it colours the entire row (first and last)

and not just the selection

helps

"Bob Phillips" wrote:

Dim rng As Range

ActiveSheet.Range("A1").CurrentRegion.Copy
With Worksheets("Sheet2")
.Activate
.Range("A1").Select
.Paste
Set rng = .Range("A1").CurrentRegion
rng.Cells(1, 1).EntireRow.Interior.ColorIndex = 38
rng(rng.Count).EntireRow.Interior.ColorIndex = 38
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)


"flow23" wrote in message
...
I want a macro..
that will select a currentregion (starting from A1) and pasting in new
sheet

but after pasting.. I want to color the first row and the last row.

The number of row varies..

How canI do it?





--

Dave Peterson


--

Dave Peterson