Many thanks it works perfect

Also can we add another conidition to it

IF cell a of the current region contains "Total".. highlight the enitre row?


"Dave Peterson" wrote:

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