Last used cell in column with shading

[Desert Piranha]

Hi all,
I use this to toggle from a cell on top of the sheet,
to the cell, one below the last used cell in Col P.

Please Login or Register  to view this content.

It works as it should. My twist is that, in Column P there may be a pattern in the cells below the last used cell.
The pattern is variable as far as the number of cells below the last used cell it will populate.
The pattern is put there by another macro, using

Please Login or Register  to view this content.

not conditional formating.
I am trying to make the above macro go to the "last used cell in Column P which includes if the cell has a pattern in it.
So say, P1:P10 have values and P11:P15 have the pattern, then my active cell address should be P16.

Any direction appreciated.

[Norman Jones]

Hi Dave,

What logic does the other macro use to shade cells?

---
Regards,
Norman



"Desert Piranha"
<[email protected]> wrote in
message news:[email protected]...
>
> Hi all,
> I use this to toggle from a cell on top of the sheet,
> to the cell, one below the last used cell in Col P.
>
> Code:
> --------------------
> Sub TopBottomToggle5()
> If ActiveCell.Address <> "$G$5" Then
> Range("G5").Select
> Else
> Range("P65536").End(xlUp).Offset(1, 0).Select
> End If
> End Sub
>
> --------------------
> It works as it should. My twist is that, in Column P there may be a
> pattern in the cells below the last used cell.
> The pattern is variable as far as the number of cells below the last
> used cell it will populate.
> The pattern is put there by another macro, using
> Code:
> --------------------
> Interior.Pattern = xlPatternGray8
> --------------------
> not conditional formating.
> I am trying to make the above macro go to the "last used cell in Column
> P which includes if the cell has a pattern in it.
> So say, P1:P10 have values and P11:P15 have the pattern, then my active
> cell address should be P16.
>
> Any direction appreciated.
>
>
> --
> Desert Piranha
>
>
> ------------------------------------------------------------------------
> Desert Piranha's Profile:
> http://www.excelforum.com/member.php...o&userid=28934
> View this thread: http://www.excelforum.com/showthread...hreadid=491114
>

[Desert Piranha]

Hi Norman,
Its the " PseudoBarChart" code that you wrote for me, where it will populate the cells in a range,
with a pattern from the last used row, up to the last used cell in another column. whew!
That make sense? want me to post it?

Hi Dave,

What logic does the other macro use to shade cells?

---
Regards,
Norman



"Desert Piranha"
<[email protected]> wrote in
message news:[email protected]...
>
> Hi all,
> I use this to toggle from a cell on top of the sheet,
> to the cell, one below the last used cell in Col P.
>
> Code:
> --------------------
> Sub TopBottomToggle5()
> If ActiveCell.Address <> "$G$5" Then
> Range("G5").Select
> Else
> Range("P65536").End(xlUp).Offset(1, 0).Select
> End If
> End Sub
>
> --------------------
> It works as it should. My twist is that, in Column P there may be a
> pattern in the cells below the last used cell.
> The pattern is variable as far as the number of cells below the last
> used cell it will populate.
> The pattern is put there by another macro, using
> Code:
> --------------------
> Interior.Pattern = xlPatternGray8
> --------------------
> not conditional formating.
> I am trying to make the above macro go to the "last used cell in Column
> P which includes if the cell has a pattern in it.
> So say, P1:P10 have values and P11:P15 have the pattern, then my active
> cell address should be P16.
>
> Any direction appreciated.
>
>
> --
> Desert Piranha
>
>
> ------------------------------------------------------------------------
> Desert Piranha's Profile:
> http://www.excelforum.com/member.php...o&userid=28934
> View this thread: http://www.excelforum.com/showthread...hreadid=491114
>

[Jim Thomlinson]

This should be close...

Sub TopBottomToggle5()
Dim rng As Range

If ActiveCell.Address <> "$G$5" Then
Range("G5").Select
Else
Set rng = Range("P65536").End(xlUp).Offset(1, 0)
If rng.Offset(1, 0).Interior.Pattern = xlPatternGray Then
Do While rng.Interior.Pattern = xlPatternGray
Set rng = rng.Offset(1, 0)
Loop
Else
rng.Select
End If
End If
End Sub
--
HTH...

Jim Thomlinson


"Desert Piranha" wrote:

>
> Hi all,
> I use this to toggle from a cell on top of the sheet,
> to the cell, one below the last used cell in Col P.
>
> Code:
> --------------------
> Sub TopBottomToggle5()
> If ActiveCell.Address <> "$G$5" Then
> Range("G5").Select
> Else
> Range("P65536").End(xlUp).Offset(1, 0).Select
> End If
> End Sub
>
> --------------------
> It works as it should. My twist is that, in Column P there may be a
> pattern in the cells below the last used cell.
> The pattern is variable as far as the number of cells below the last
> used cell it will populate.
> The pattern is put there by another macro, using
> Code:
> --------------------
> Interior.Pattern = xlPatternGray8
> --------------------
> not conditional formating.
> I am trying to make the above macro go to the "last used cell in Column
> P which includes if the cell has a pattern in it.
> So say, P1:P10 have values and P11:P15 have the pattern, then my active
> cell address should be P16.
>
> Any direction appreciated.
>
>
> --
> Desert Piranha
>
>
> ------------------------------------------------------------------------
> Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934
> View this thread: http://www.excelforum.com/showthread...hreadid=491114
>
>

[Norman Jones]

Hi Dave,

Try:

'============>>
Sub TopBottomToggle5()
If ActiveCell.Address <> "$G$5" Then
Intersect(Cells.SpecialCells(xlCellTypeLastCell). _
EntireRow, Columns("P")).Select
End If
End Sub
'<<============

---
Regards,
Norman


"Desert Piranha"
<[email protected]> wrote in
message news:[email protected]...
>
> Hi Norman,
> Its the " PseudoBarChart" code that you wrote for me, where it will
> populate the cells in a range,
> with a pattern from the last used row, up to the last used cell in
> another column. whew!
> That make sense? want me to post it?Norman Jones Wrote:
>> Hi Dave,
>>
>> What logic does the other macro use to shade cells?
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "Desert Piranha"
>> <[email protected]> wrote in
>> message
>> news:[email protected]...
>> >
>> > Hi all,
>> > I use this to toggle from a cell on top of the sheet,
>> > to the cell, one below the last used cell in Col P.
>> >
>> > Code:
>> > --------------------
>> > Sub TopBottomToggle5()
>> > If ActiveCell.Address <> "$G$5" Then
>> > Range("G5").Select
>> > Else
>> > Range("P65536").End(xlUp).Offset(1, 0).Select
>> > End If
>> > End Sub
>> >
>> > --------------------
>> > It works as it should. My twist is that, in Column P there may be a
>> > pattern in the cells below the last used cell.
>> > The pattern is variable as far as the number of cells below the last
>> > used cell it will populate.
>> > The pattern is put there by another macro, using
>> > Code:
>> > --------------------
>> > Interior.Pattern = xlPatternGray8
>> > --------------------
>> > not conditional formating.
>> > I am trying to make the above macro go to the "last used cell in
>> Column
>> > P which includes if the cell has a pattern in it.
>> > So say, P1:P10 have values and P11:P15 have the pattern, then my
>> active
>> > cell address should be P16.
>> >
>> > Any direction appreciated.
>> >
>> >
>> > --
>> > Desert Piranha
>> >
>> >
>> >
>> ------------------------------------------------------------------------
>> > Desert Piranha's Profile:
>> > http://www.excelforum.com/member.php...o&userid=28934
>> > View this thread:
>> http://www.excelforum.com/showthread...hreadid=491114
>> >
>
>
> --
> Desert Piranha
>
>
> ------------------------------------------------------------------------
> Desert Piranha's Profile:
> http://www.excelforum.com/member.php...o&userid=28934
> View this thread: http://www.excelforum.com/showthread...hreadid=491114
>

[Desert Piranha]

Hi Norman,
I added line 3 & 4 to the macro, so as to toggle, But on the column P it does not go to the cell
directly below the pattern/value. Instead it goes way down the column to a unrelated cell.
There are formulas in other columns on the page and i think its going down past those,
not stoping at the Pattern/Value in Column P
(Sorry i am not good at explaining)
Sub TopBottomToggle5()
If ActiveCell.Address <> "$G$5" Then
Range("G5").Select
Else
Intersect(Cells.SpecialCells(xlCellTypeLastCell). _
EntireRow, Columns("P")).Select
End If
End Sub

Hi Dave,
Try:

'============>>
Sub TopBottomToggle5()
If ActiveCell.Address <> "$G$5" Then
Intersect(Cells.SpecialCells(xlCellTypeLastCell). _
EntireRow, Columns("P")).Select
End If
End Sub
'<<============

---
Regards,
Norman


"Desert Piranha"
<[email protected]> wrote in
message news:[email protected]...
>
> Hi Norman,
> Its the " PseudoBarChart" code that you wrote for me, where it will
> populate the cells in a range,
> with a pattern from the last used row, up to the last used cell in
> another column. whew!
> That make sense? want me to post it?Norman Jones Wrote:
>> Hi Dave,
>>
>> What logic does the other macro use to shade cells?
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "Desert Piranha"
>> <[email protected]> wrote in
>> message
>> news:[email protected]...
>> >
>> > Hi all,
>> > I use this to toggle from a cell on top of the sheet,
>> > to the cell, one below the last used cell in Col P.
>> >
>> > Code:
>> > --------------------
>> > Sub TopBottomToggle5()
>> > If ActiveCell.Address <> "$G$5" Then
>> > Range("G5").Select
>> > Else
>> > Range("P65536").End(xlUp).Offset(1, 0).Select
>> > End If
>> > End Sub
>> >
>> > --------------------
>> > It works as it should. My twist is that, in Column P there may be a
>> > pattern in the cells below the last used cell.
>> > The pattern is variable as far as the number of cells below the last
>> > used cell it will populate.
>> > The pattern is put there by another macro, using
>> > Code:
>> > --------------------
>> > Interior.Pattern = xlPatternGray8
>> > --------------------
>> > not conditional formating.
>> > I am trying to make the above macro go to the "last used cell in
>> Column
>> > P which includes if the cell has a pattern in it.
>> > So say, P1:P10 have values and P11:P15 have the pattern, then my
>> active
>> > cell address should be P16.
>> >
>> > Any direction appreciated.
>> >
>> >
>> > --
>> > Desert Piranha
>> >
>> >
>> >
>> ------------------------------------------------------------------------
>> > Desert Piranha's Profile:
>> > http://www.excelforum.com/member.php...o&userid=28934
>> > View this thread:
>> http://www.excelforum.com/showthread...hreadid=491114
>> >
>
>
> --
> Desert Piranha
>
>
> ------------------------------------------------------------------------
> Desert Piranha's Profile:
> http://www.excelforum.com/member.php...o&userid=28934
> View this thread: http://www.excelforum.com/showthread...hreadid=491114
>

[Desert Piranha]

Hi Jim,
As its written, it does the same thing mine does. I am monkeying with it, will let you know.

This should be close...

Sub TopBottomToggle5()
Dim rng As Range

If ActiveCell.Address <> "$G$5" Then
Range("G5").Select
Else
Set rng = Range("P65536").End(xlUp).Offset(1, 0)
If rng.Offset(1, 0).Interior.Pattern = xlPatternGray Then
Do While rng.Interior.Pattern = xlPatternGray
Set rng = rng.Offset(1, 0)
Loop
Else
rng.Select
End If
End If
End Sub
--
HTH...

Jim Thomlinson


"Desert Piranha" wrote:

>
> Hi all,
> I use this to toggle from a cell on top of the sheet,
> to the cell, one below the last used cell in Col P.
>
> Code:
> --------------------
> Sub TopBottomToggle5()
> If ActiveCell.Address <> "$G$5" Then
> Range("G5").Select
> Else
> Range("P65536").End(xlUp).Offset(1, 0).Select
> End If
> End Sub
>
> --------------------
> It works as it should. My twist is that, in Column P there may be a
> pattern in the cells below the last used cell.
> The pattern is variable as far as the number of cells below the last
> used cell it will populate.
> The pattern is put there by another macro, using
> Code:
> --------------------
> Interior.Pattern = xlPatternGray8
> --------------------
> not conditional formating.
> I am trying to make the above macro go to the "last used cell in Column
> P which includes if the cell has a pattern in it.
> So say, P1:P10 have values and P11:P15 have the pattern, then my active
> cell address should be P16.
>
> Any direction appreciated.
>
>
> --
> Desert Piranha
>
>
> ------------------------------------------------------------------------
> Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934
> View this thread: http://www.excelforum.com/showthread...hreadid=491114
>
>

[Jim Thomlinson]

Give this a whirl. It looks for cells shaded light grey.

Sub TopBottomToggle5()
Dim rng As Range

If ActiveCell.Address <> "$G$5" Then
Range("G5").Select
Else
Set rng = Range("P65536").End(xlUp).Offset(1, 0)
If rng.Interior.ColorIndex = 15 Then
Do While rng.Interior.ColorIndex = 15
Set rng = rng.Offset(1, 0)
Loop
End If
rng.Select
End If
End Sub
--
HTH...

Jim Thomlinson


"Desert Piranha" wrote:

>
> Hi Jim,
> As its written, it does the same thing mine does. I am monkeying with
> it, will let you know.
> Jim Thomlinson Wrote:
> > This should be close...
> >
> > Sub TopBottomToggle5()
> > Dim rng As Range
> >
> > If ActiveCell.Address <> "$G$5" Then
> > Range("G5").Select
> > Else
> > Set rng = Range("P65536").End(xlUp).Offset(1, 0)
> > If rng.Offset(1, 0).Interior.Pattern = xlPatternGray Then
> > Do While rng.Interior.Pattern = xlPatternGray
> > Set rng = rng.Offset(1, 0)
> > Loop
> > Else
> > rng.Select
> > End If
> > End If
> > End Sub
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Desert Piranha" wrote:
> >
> > >
> > > Hi all,
> > > I use this to toggle from a cell on top of the sheet,
> > > to the cell, one below the last used cell in Col P.
> > >
> > > Code:
> > > --------------------
> > > Sub TopBottomToggle5()
> > > If ActiveCell.Address <> "$G$5" Then
> > > Range("G5").Select
> > > Else
> > > Range("P65536").End(xlUp).Offset(1, 0).Select
> > > End If
> > > End Sub
> > >
> > > --------------------
> > > It works as it should. My twist is that, in Column P there may be a
> > > pattern in the cells below the last used cell.
> > > The pattern is variable as far as the number of cells below the last
> > > used cell it will populate.
> > > The pattern is put there by another macro, using
> > > Code:
> > > --------------------
> > > Interior.Pattern = xlPatternGray8
> > > --------------------
> > > not conditional formating.
> > > I am trying to make the above macro go to the "last used cell in
> > Column
> > > P which includes if the cell has a pattern in it.
> > > So say, P1:P10 have values and P11:P15 have the pattern, then my
> > active
> > > cell address should be P16.
> > >
> > > Any direction appreciated.
> > >
> > >
> > > --
> > > Desert Piranha
> > >
> > >
> > >
> > ------------------------------------------------------------------------
> > > Desert Piranha's Profile:
> > http://www.excelforum.com/member.php...o&userid=28934
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=491114
> > >
> > >
>
>
> --
> Desert Piranha
>
>
> ------------------------------------------------------------------------
> Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934
> View this thread: http://www.excelforum.com/showthread...hreadid=491114
>
>

[Norman Jones]

Hi Dave,

Try,

'============>>
Sub TopBottomToggle5()
If ActiveCell.Address <> "$G$5" Then
Range("G5").Select
Else
Range("P" & LastRow(ActiveSheet, Cells))(2).Select
End If
End Sub
'<<============


You already use the LastRow function in your workbook, but, for
completeness:

'=============>>
Function LastRow(sh As Worksheet, RngF As Range)
On Error Resume Next
LastRow = RngF.Find(What:="*", _
After:=RngF.Cells(RngF.Cells.Count), _
Lookat:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function
'<<============


---
Regards,
Norman



"Desert Piranha"
<[email protected]> wrote in
message news:[email protected]...
>
> Hi Norman,
> I added line 3 & 4 to the macro, so as to toggle, But on the column P
> it does not go to the cell
> directly below the pattern/value. Instead it goes way down the column
> to a unrelated cell.
> There are formulas in other columns on the page and i think its going
> down past those,
> not stoping at the Pattern/Value in Column P
> (Sorry i am not good at explaining)
> Sub TopBottomToggle5()
> If ActiveCell.Address <> "$G$5" Then
> Range("G5").Select
> Else
> Intersect(Cells.SpecialCells(xlCellTypeLastCell). _
> EntireRow, Columns("P")).Select
> End If
> End Sub
>
> Norman Jones Wrote:
>> Hi Dave,
>> Try:
>>
>> '============>>
>> Sub TopBottomToggle5()
>> If ActiveCell.Address <> "$G$5" Then
>> Intersect(Cells.SpecialCells(xlCellTypeLastCell). _
>> EntireRow, Columns("P")).Select
>> End If
>> End Sub
>> '<<============
>>
>> ---
>> Regards,
>> Norman
>>
>>
>> "Desert Piranha"
>> <[email protected]> wrote in
>> message
>> news:[email protected]...
>> >
>> > Hi Norman,
>> > Its the " PseudoBarChart" code that you wrote for me, where it will
>> > populate the cells in a range,
>> > with a pattern from the last used row, up to the last used cell in
>> > another column. whew!
>> > That make sense? want me to post it?Norman Jones Wrote:
>> >> Hi Dave,
>> >>
>> >> What logic does the other macro use to shade cells?
>> >>
>> >> ---
>> >> Regards,
>> >> Norman
>> >>
>> >>
>> >>
>> >> "Desert Piranha"
>> >> <[email protected]> wrote
>> in
>> >> message
>> >> news:[email protected]...
>> >> >
>> >> > Hi all,
>> >> > I use this to toggle from a cell on top of the sheet,
>> >> > to the cell, one below the last used cell in Col P.
>> >> >
>> >> > Code:
>> >> > --------------------
>> >> > Sub TopBottomToggle5()
>> >> > If ActiveCell.Address <> "$G$5" Then
>> >> > Range("G5").Select
>> >> > Else
>> >> > Range("P65536").End(xlUp).Offset(1, 0).Select
>> >> > End If
>> >> > End Sub
>> >> >
>> >> > --------------------
>> >> > It works as it should. My twist is that, in Column P there may be
>> a
>> >> > pattern in the cells below the last used cell.
>> >> > The pattern is variable as far as the number of cells below the
>> last
>> >> > used cell it will populate.
>> >> > The pattern is put there by another macro, using
>> >> > Code:
>> >> > --------------------
>> >> > Interior.Pattern = xlPatternGray8
>> >> > --------------------
>> >> > not conditional formating.
>> >> > I am trying to make the above macro go to the "last used cell in
>> >> Column
>> >> > P which includes if the cell has a pattern in it.
>> >> > So say, P1:P10 have values and P11:P15 have the pattern, then my
>> >> active
>> >> > cell address should be P16.
>> >> >
>> >> > Any direction appreciated.
>> >> >
>> >> >
>> >> > --
>> >> > Desert Piranha
>> >> >
>> >> >
>> >> >
>> >>
>> ------------------------------------------------------------------------
>> >> > Desert Piranha's Profile:
>> >> > http://www.excelforum.com/member.php...o&userid=28934
>> >> > View this thread:
>> >> http://www.excelforum.com/showthread...hreadid=491114
>> >> >
>> >
>> >
>> > --
>> > Desert Piranha
>> >
>> >
>> >
>> ------------------------------------------------------------------------
>> > Desert Piranha's Profile:
>> > http://www.excelforum.com/member.php...o&userid=28934
>> > View this thread:
>> http://www.excelforum.com/showthread...hreadid=491114
>> >
>
>
> --
> Desert Piranha
>
>
> ------------------------------------------------------------------------
> Desert Piranha's Profile:
> http://www.excelforum.com/member.php...o&userid=28934
> View this thread: http://www.excelforum.com/showthread...hreadid=491114
>

[Norman Jones]

Hi Jim,

Just by way of insider information, Dave's shading is ColorIndex 15, as used
in your present code.

Also, your code works for me, using the file which (I think) Dave refers to.

---
Regards,
Norman



"Jim Thomlinson" <[email protected]> wrote in message
news:[email protected]...
> Give this a whirl. It looks for cells shaded light grey.
>
> Sub TopBottomToggle5()
> Dim rng As Range
>
> If ActiveCell.Address <> "$G$5" Then
> Range("G5").Select
> Else
> Set rng = Range("P65536").End(xlUp).Offset(1, 0)
> If rng.Interior.ColorIndex = 15 Then
> Do While rng.Interior.ColorIndex = 15
> Set rng = rng.Offset(1, 0)
> Loop
> End If
> rng.Select
> End If
> End Sub
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Desert Piranha" wrote:
>
>>
>> Hi Jim,
>> As its written, it does the same thing mine does. I am monkeying with
>> it, will let you know.
>> Jim Thomlinson Wrote:
>> > This should be close...
>> >
>> > Sub TopBottomToggle5()
>> > Dim rng As Range
>> >
>> > If ActiveCell.Address <> "$G$5" Then
>> > Range("G5").Select
>> > Else
>> > Set rng = Range("P65536").End(xlUp).Offset(1, 0)
>> > If rng.Offset(1, 0).Interior.Pattern = xlPatternGray Then
>> > Do While rng.Interior.Pattern = xlPatternGray
>> > Set rng = rng.Offset(1, 0)
>> > Loop
>> > Else
>> > rng.Select
>> > End If
>> > End If
>> > End Sub
>> > --
>> > HTH...
>> >
>> > Jim Thomlinson
>> >
>> >
>> > "Desert Piranha" wrote:
>> >
>> > >
>> > > Hi all,
>> > > I use this to toggle from a cell on top of the sheet,
>> > > to the cell, one below the last used cell in Col P.
>> > >
>> > > Code:
>> > > --------------------
>> > > Sub TopBottomToggle5()
>> > > If ActiveCell.Address <> "$G$5" Then
>> > > Range("G5").Select
>> > > Else
>> > > Range("P65536").End(xlUp).Offset(1, 0).Select
>> > > End If
>> > > End Sub
>> > >
>> > > --------------------
>> > > It works as it should. My twist is that, in Column P there may be a
>> > > pattern in the cells below the last used cell.
>> > > The pattern is variable as far as the number of cells below the last
>> > > used cell it will populate.
>> > > The pattern is put there by another macro, using
>> > > Code:
>> > > --------------------
>> > > Interior.Pattern = xlPatternGray8
>> > > --------------------
>> > > not conditional formating.
>> > > I am trying to make the above macro go to the "last used cell in
>> > Column
>> > > P which includes if the cell has a pattern in it.
>> > > So say, P1:P10 have values and P11:P15 have the pattern, then my
>> > active
>> > > cell address should be P16.
>> > >
>> > > Any direction appreciated.
>> > >
>> > >
>> > > --
>> > > Desert Piranha
>> > >
>> > >
>> > >
>> > ------------------------------------------------------------------------
>> > > Desert Piranha's Profile:
>> > http://www.excelforum.com/member.php...o&userid=28934
>> > > View this thread:
>> > http://www.excelforum.com/showthread...hreadid=491114
>> > >
>> > >
>>
>>
>> --
>> Desert Piranha
>>
>>
>> ------------------------------------------------------------------------
>> Desert Piranha's Profile:
>> http://www.excelforum.com/member.php...o&userid=28934
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=491114
>>
>>

[Jim Thomlinson]

I think you last solution is going to have a problem in that it looks for the
last populated cell (Find * in LastRow function). The shaded cells are not
populated (I assume as a guess). I never use last cell. In my opinion it is
darn near useless since you can not guarantee where it will be at any given
time based on changes the user may have made. By the way since I don't think
I have mentioned it before, in general I like your code. Definitely above
average with no bad habits...
--
HTH...

Jim Thomlinson


"Norman Jones" wrote:

> Hi Jim,
>
> Just by way of insider information, Dave's shading is ColorIndex 15, as used
> in your present code.
>
> Also, your code works for me, using the file which (I think) Dave refers to.
>
> ---
> Regards,
> Norman
>
>
>
> "Jim Thomlinson" <[email protected]> wrote in message
> news:[email protected]...
> > Give this a whirl. It looks for cells shaded light grey.
> >
> > Sub TopBottomToggle5()
> > Dim rng As Range
> >
> > If ActiveCell.Address <> "$G$5" Then
> > Range("G5").Select
> > Else
> > Set rng = Range("P65536").End(xlUp).Offset(1, 0)
> > If rng.Interior.ColorIndex = 15 Then
> > Do While rng.Interior.ColorIndex = 15
> > Set rng = rng.Offset(1, 0)
> > Loop
> > End If
> > rng.Select
> > End If
> > End Sub
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Desert Piranha" wrote:
> >
> >>
> >> Hi Jim,
> >> As its written, it does the same thing mine does. I am monkeying with
> >> it, will let you know.
> >> Jim Thomlinson Wrote:
> >> > This should be close...
> >> >
> >> > Sub TopBottomToggle5()
> >> > Dim rng As Range
> >> >
> >> > If ActiveCell.Address <> "$G$5" Then
> >> > Range("G5").Select
> >> > Else
> >> > Set rng = Range("P65536").End(xlUp).Offset(1, 0)
> >> > If rng.Offset(1, 0).Interior.Pattern = xlPatternGray Then
> >> > Do While rng.Interior.Pattern = xlPatternGray
> >> > Set rng = rng.Offset(1, 0)
> >> > Loop
> >> > Else
> >> > rng.Select
> >> > End If
> >> > End If
> >> > End Sub
> >> > --
> >> > HTH...
> >> >
> >> > Jim Thomlinson
> >> >
> >> >
> >> > "Desert Piranha" wrote:
> >> >
> >> > >
> >> > > Hi all,
> >> > > I use this to toggle from a cell on top of the sheet,
> >> > > to the cell, one below the last used cell in Col P.
> >> > >
> >> > > Code:
> >> > > --------------------
> >> > > Sub TopBottomToggle5()
> >> > > If ActiveCell.Address <> "$G$5" Then
> >> > > Range("G5").Select
> >> > > Else
> >> > > Range("P65536").End(xlUp).Offset(1, 0).Select
> >> > > End If
> >> > > End Sub
> >> > >
> >> > > --------------------
> >> > > It works as it should. My twist is that, in Column P there may be a
> >> > > pattern in the cells below the last used cell.
> >> > > The pattern is variable as far as the number of cells below the last
> >> > > used cell it will populate.
> >> > > The pattern is put there by another macro, using
> >> > > Code:
> >> > > --------------------
> >> > > Interior.Pattern = xlPatternGray8
> >> > > --------------------
> >> > > not conditional formating.
> >> > > I am trying to make the above macro go to the "last used cell in
> >> > Column
> >> > > P which includes if the cell has a pattern in it.
> >> > > So say, P1:P10 have values and P11:P15 have the pattern, then my
> >> > active
> >> > > cell address should be P16.
> >> > >
> >> > > Any direction appreciated.
> >> > >
> >> > >
> >> > > --
> >> > > Desert Piranha
> >> > >
> >> > >
> >> > >
> >> > ------------------------------------------------------------------------
> >> > > Desert Piranha's Profile:
> >> > http://www.excelforum.com/member.php...o&userid=28934
> >> > > View this thread:
> >> > http://www.excelforum.com/showthread...hreadid=491114
> >> > >
> >> > >
> >>
> >>
> >> --
> >> Desert Piranha
> >>
> >>
> >> ------------------------------------------------------------------------
> >> Desert Piranha's Profile:
> >> http://www.excelforum.com/member.php...o&userid=28934
> >> View this thread:
> >> http://www.excelforum.com/showthread...hreadid=491114
> >>
> >>
>
>
>

[Tom Ogilvy]

It may appear useless (if not understood), but it will include the shaded
cells of which you speak.

--
Regards,
Tom Ogivy


"Jim Thomlinson" <[email protected]> wrote in message
news:[email protected]...
> I think you last solution is going to have a problem in that it looks for
the
> last populated cell (Find * in LastRow function). The shaded cells are not
> populated (I assume as a guess). I never use last cell. In my opinion it
is
> darn near useless since you can not guarantee where it will be at any
given
> time based on changes the user may have made. By the way since I don't
think
> I have mentioned it before, in general I like your code. Definitely above
> average with no bad habits...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Norman Jones" wrote:
>
> > Hi Jim,
> >
> > Just by way of insider information, Dave's shading is ColorIndex 15, as
used
> > in your present code.
> >
> > Also, your code works for me, using the file which (I think) Dave refers
to.
> >
> > ---
> > Regards,
> > Norman
> >
> >
> >
> > "Jim Thomlinson" <[email protected]> wrote in message
> > news:[email protected]...
> > > Give this a whirl. It looks for cells shaded light grey.
> > >
> > > Sub TopBottomToggle5()
> > > Dim rng As Range
> > >
> > > If ActiveCell.Address <> "$G$5" Then
> > > Range("G5").Select
> > > Else
> > > Set rng = Range("P65536").End(xlUp).Offset(1, 0)
> > > If rng.Interior.ColorIndex = 15 Then
> > > Do While rng.Interior.ColorIndex = 15
> > > Set rng = rng.Offset(1, 0)
> > > Loop
> > > End If
> > > rng.Select
> > > End If
> > > End Sub
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "Desert Piranha" wrote:
> > >
> > >>
> > >> Hi Jim,
> > >> As its written, it does the same thing mine does. I am monkeying with
> > >> it, will let you know.
> > >> Jim Thomlinson Wrote:
> > >> > This should be close...
> > >> >
> > >> > Sub TopBottomToggle5()
> > >> > Dim rng As Range
> > >> >
> > >> > If ActiveCell.Address <> "$G$5" Then
> > >> > Range("G5").Select
> > >> > Else
> > >> > Set rng = Range("P65536").End(xlUp).Offset(1, 0)
> > >> > If rng.Offset(1, 0).Interior.Pattern = xlPatternGray Then
> > >> > Do While rng.Interior.Pattern = xlPatternGray
> > >> > Set rng = rng.Offset(1, 0)
> > >> > Loop
> > >> > Else
> > >> > rng.Select
> > >> > End If
> > >> > End If
> > >> > End Sub
> > >> > --
> > >> > HTH...
> > >> >
> > >> > Jim Thomlinson
> > >> >
> > >> >
> > >> > "Desert Piranha" wrote:
> > >> >
> > >> > >
> > >> > > Hi all,
> > >> > > I use this to toggle from a cell on top of the sheet,
> > >> > > to the cell, one below the last used cell in Col P.
> > >> > >
> > >> > > Code:
> > >> > > --------------------
> > >> > > Sub TopBottomToggle5()
> > >> > > If ActiveCell.Address <> "$G$5" Then
> > >> > > Range("G5").Select
> > >> > > Else
> > >> > > Range("P65536").End(xlUp).Offset(1, 0).Select
> > >> > > End If
> > >> > > End Sub
> > >> > >
> > >> > > --------------------
> > >> > > It works as it should. My twist is that, in Column P there may be
a
> > >> > > pattern in the cells below the last used cell.
> > >> > > The pattern is variable as far as the number of cells below the
last
> > >> > > used cell it will populate.
> > >> > > The pattern is put there by another macro, using
> > >> > > Code:
> > >> > > --------------------
> > >> > > Interior.Pattern = xlPatternGray8
> > >> > > --------------------
> > >> > > not conditional formating.
> > >> > > I am trying to make the above macro go to the "last used cell in
> > >> > Column
> > >> > > P which includes if the cell has a pattern in it.
> > >> > > So say, P1:P10 have values and P11:P15 have the pattern, then my
> > >> > active
> > >> > > cell address should be P16.
> > >> > >
> > >> > > Any direction appreciated.
> > >> > >
> > >> > >
> > >> > > --
> > >> > > Desert Piranha
> > >> > >
> > >> > >
> > >> > >
> > >>
> ------------------------------------------------------------------------
> > >> > > Desert Piranha's Profile:
> > >> > http://www.excelforum.com/member.php...o&userid=28934
> > >> > > View this thread:
> > >> > http://www.excelforum.com/showthread...hreadid=491114
> > >> > >
> > >> > >
> > >>
> > >>
> > >> --
> > >> Desert Piranha
> > >>
> > >>
> >
>> ------------------------------------------------------------------------
> > >> Desert Piranha's Profile:
> > >> http://www.excelforum.com/member.php...o&userid=28934
> > >> View this thread:
> > >> http://www.excelforum.com/showthread...hreadid=491114
> > >>
> > >>
> >
> >
> >

[Norman Jones]

Hi Jim,

>I think you last solution is going to have a problem in that it looks
> for the last populated cell (Find * in LastRow function). The
> shaded cells are not populated (I assume as a guess).

Dave's scenario is that each column's blank cells are shaded down to, and
including, the last populated row.

Consequently, the LastRow function was appropriate and, testing on Dave's
original file, it returned the expected results when used in conjunction
with my suggested code.

As, already indicated, your code also worked for me - and you did not have
the advantage of having (an admittedly very old copy of ) the file hidden
away in my archives.


---
Regards,
Norman



"Jim Thomlinson" <[email protected]> wrote in message
news:[email protected]...
>I think you last solution is going to have a problem in that it looks for
>the
> last populated cell (Find * in LastRow function). The shaded cells are not
> populated (I assume as a guess). I never use last cell. In my opinion it
> is
> darn near useless since you can not guarantee where it will be at any
> given
> time based on changes the user may have made. By the way since I don't
> think
> I have mentioned it before, in general I like your code. Definitely above
> average with no bad habits...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Norman Jones" wrote:
>
>> Hi Jim,
>>
>> Just by way of insider information, Dave's shading is ColorIndex 15, as
>> used
>> in your present code.
>>
>> Also, your code works for me, using the file which (I think) Dave refers
>> to.
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "Jim Thomlinson" <[email protected]> wrote in message
>> news:[email protected]...
>> > Give this a whirl. It looks for cells shaded light grey.
>> >
>> > Sub TopBottomToggle5()
>> > Dim rng As Range
>> >
>> > If ActiveCell.Address <> "$G$5" Then
>> > Range("G5").Select
>> > Else
>> > Set rng = Range("P65536").End(xlUp).Offset(1, 0)
>> > If rng.Interior.ColorIndex = 15 Then
>> > Do While rng.Interior.ColorIndex = 15
>> > Set rng = rng.Offset(1, 0)
>> > Loop
>> > End If
>> > rng.Select
>> > End If
>> > End Sub
>> > --
>> > HTH...
>> >
>> > Jim Thomlinson
>> >
>> >
>> > "Desert Piranha" wrote:
>> >
>> >>
>> >> Hi Jim,
>> >> As its written, it does the same thing mine does. I am monkeying with
>> >> it, will let you know.
>> >> Jim Thomlinson Wrote:
>> >> > This should be close...
>> >> >
>> >> > Sub TopBottomToggle5()
>> >> > Dim rng As Range
>> >> >
>> >> > If ActiveCell.Address <> "$G$5" Then
>> >> > Range("G5").Select
>> >> > Else
>> >> > Set rng = Range("P65536").End(xlUp).Offset(1, 0)
>> >> > If rng.Offset(1, 0).Interior.Pattern = xlPatternGray Then
>> >> > Do While rng.Interior.Pattern = xlPatternGray
>> >> > Set rng = rng.Offset(1, 0)
>> >> > Loop
>> >> > Else
>> >> > rng.Select
>> >> > End If
>> >> > End If
>> >> > End Sub
>> >> > --
>> >> > HTH...
>> >> >
>> >> > Jim Thomlinson
>> >> >
>> >> >
>> >> > "Desert Piranha" wrote:
>> >> >
>> >> > >
>> >> > > Hi all,
>> >> > > I use this to toggle from a cell on top of the sheet,
>> >> > > to the cell, one below the last used cell in Col P.
>> >> > >
>> >> > > Code:
>> >> > > --------------------
>> >> > > Sub TopBottomToggle5()
>> >> > > If ActiveCell.Address <> "$G$5" Then
>> >> > > Range("G5").Select
>> >> > > Else
>> >> > > Range("P65536").End(xlUp).Offset(1, 0).Select
>> >> > > End If
>> >> > > End Sub
>> >> > >
>> >> > > --------------------
>> >> > > It works as it should. My twist is that, in Column P there may be
>> >> > > a
>> >> > > pattern in the cells below the last used cell.
>> >> > > The pattern is variable as far as the number of cells below the
>> >> > > last
>> >> > > used cell it will populate.
>> >> > > The pattern is put there by another macro, using
>> >> > > Code:
>> >> > > --------------------
>> >> > > Interior.Pattern = xlPatternGray8
>> >> > > --------------------
>> >> > > not conditional formating.
>> >> > > I am trying to make the above macro go to the "last used cell in
>> >> > Column
>> >> > > P which includes if the cell has a pattern in it.
>> >> > > So say, P1:P10 have values and P11:P15 have the pattern, then my
>> >> > active
>> >> > > cell address should be P16.
>> >> > >
>> >> > > Any direction appreciated.
>> >> > >
>> >> > >
>> >> > > --
>> >> > > Desert Piranha
>> >> > >
>> >> > >
>> >> > >
>> >> > ------------------------------------------------------------------------
>> >> > > Desert Piranha's Profile:
>> >> > http://www.excelforum.com/member.php...o&userid=28934
>> >> > > View this thread:
>> >> > http://www.excelforum.com/showthread...hreadid=491114
>> >> > >
>> >> > >
>> >>
>> >>
>> >> --
>> >> Desert Piranha
>> >>
>> >>
>> >> ------------------------------------------------------------------------
>> >> Desert Piranha's Profile:
>> >> http://www.excelforum.com/member.php...o&userid=28934
>> >> View this thread:
>> >> http://www.excelforum.com/showthread...hreadid=491114
>> >>
>> >>
>>
>>
>>

[Norman Jones]

Hi Jim,

To remove possible ambiguity:

> Dave's scenario is that each column's blank cells are shaded down to, and
> including, the last populated row

would have been better expressesd as:

Dave's scenario is that each column's blank cells are shaded down to, and
including, the last populated row on the sheet.


---
Regards,
Norman



"Norman Jones" <[email protected]> wrote in message
news:eVFw7$p%[email protected]...
> Hi Jim,
>
>>I think you last solution is going to have a problem in that it looks
>> for the last populated cell (Find * in LastRow function). The
>> shaded cells are not populated (I assume as a guess).
>
> Dave's scenario is that each column's blank cells are shaded down to, and
> including, the last populated row.
>
> Consequently, the LastRow function was appropriate and, testing on Dave's
> original file, it returned the expected results when used in conjunction
> with my suggested code.
>
> As, already indicated, your code also worked for me - and you did not have
> the advantage of having (an admittedly very old copy of ) the file hidden
> away in my archives.
>
>
> ---
> Regards,
> Norman
>

[Desert Piranha]

Ok Gentlemen,
Norman, You didn't know but I have changed my color/pattern
from "ColorIndex 15" to "Interior.Pattern = xlPatternGray8"

Norman, This of yours, works fine. I changed in line 5 the (2 to 1)
'============>>
Sub TopBottomToggle5()
If ActiveCell.Address <> "$G$5" Then
Range("G5").Select
Else
'Range("P" & LastRow(ActiveSheet, Cells))(2).Select
'Above line changed to
Range("P" & LastRow(ActiveSheet, Cells))(1).Select
End If
End Sub
'<<============
'=============>>
Function LastRow(sh As Worksheet, RngF As Range)
On Error Resume Next
LastRow = RngF.Find(What:="*", _
After:=RngF.Cells(RngF.Cells.Count), _
Lookat:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function
'<<============

Jim, This of yours, works fine. I changed the lines with the color/pattern

Sub TopBottomToggle5()
Dim rng As Range
If ActiveCell.Address <> "$G$5" Then
Range("G5").Select
Else
Set rng = Range("P65536").End(xlUp).Offset(1, 0)
' '##OLD color## If rng.Interior.ColorIndex = 15 Then
If rng.Interior.Pattern = xlPatternGray8 Then
' '##OLD color## Do While rng.Interior.ColorIndex = 15
Do While rng.Interior.Pattern = xlPatternGray8
Set rng = rng.Offset(1, 0)
Loop
End If
rng.Select
End If
End Sub

Thank you very much Norman & Jim. I have learned much from you.
Dave