currentregion

[flow23]

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?

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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?
>

[flow23]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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]

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.count) _
.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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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

[flow23]

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.count) _
> .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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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]

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.count) _
> > .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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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

[flow23]

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.count) _
> > > .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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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
>

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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.count) _
> > > > .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" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > 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
> >

[flow23]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.count) _
> > > > > .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" <[email protected]> wrote in message
> > > > > > > news:[email protected]...
> > > > > > > > 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
> > >
>
>
>

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.count) _
> > > > > > .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" <[email protected]> wrote in message
> > > > > > > > news:[email protected]...
> > > > > > > > > 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
> > > >
> >
> >
> >

[flow23]

thanks it works now
but it still highlights from column A.. where as the currentregions tstarts
from column c



"Bob Phillips" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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.count) _
> > > > > > > .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" <[email protected]> wrote in message
> > > > > > > > > news:[email protected]...
> > > > > > > > > > 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
> > > > >
> > >
> > >
> > >
>
>
>

[Dave Peterson]

Option Explicit
Sub testme01()

Dim rng As Range
Dim DestCell As Range
Dim cell 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

For Each cell In DestCell.CurrentRegion
If LCase(cell.Value) Like LCase("*Total*") Then
Intersect(DestCell.CurrentRegion, cell.EntireRow) _
.Interior.ColorIndex = 38
End If
Next cell

End Sub

flow23 wrote:
>
> thanks it works now
> but it still highlights from column A.. where as the currentregions tstarts
> from column c
>
> "Bob Phillips" wrote:
>
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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.count) _
> > > > > > > > .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" <[email protected]> wrote in message
> > > > > > > > > > news:[email protected]...
> > > > > > > > > > > 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
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

--

Dave Peterson

[Bob Phillips]

You asked for the entirerow!

--

HTH

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


"flow23" <[email protected]> wrote in message
news:[email protected]...
> thanks it works now
> but it still highlights from column A.. where as the currentregions
tstarts
> from column c
>
>
>
> "Bob Phillips" wrote:
>
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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.count)
_
> > > > > > > > .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" <[email protected]> wrote in
message
> > > > > > > > > >
news:[email protected]...
> > > > > > > > > > > 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
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

[Dave Peterson]

multiple times
<vvbg>

Bob Phillips wrote:
>
> You asked for the entirerow!
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
> "flow23" <[email protected]> wrote in message
> news:[email protected]...
> > thanks it works now
> > but it still highlights from column A.. where as the currentregions
> tstarts
> > from column c
> >
> >
> >
> > "Bob Phillips" wrote:
> >
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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.count)
> _
> > > > > > > > > .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" <[email protected]> wrote in
> message
> > > > > > > > > > >
> news:[email protected]...
> > > > > > > > > > > > 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
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >

--

Dave Peterson