Dim cell As Range
For Each cell In DestCell.CurrentRegion
If cell.Value Like "*Total*" Then
cell.EntireRow.Interior.ColorIndex = 38
End If
Next cell

--

HTH

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


"flow23" wrote in message
...
Thanks

a small glitch though

The cell contains not only the work Total but maybe "aa45 Total" or
various
other options

Can we use wild character?


"Bob Phillips" wrote:

Tack this on at the end

Dim cell As Range
For Each cell In DestCell.CurrentRegion
If cell.Value = "Total" Then
cell.EntireRow.Interior.ColorIndex = 38
End If
Next cell


--

HTH

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


"flow23" wrote in message
...
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