| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last used cell in column with shading
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 pattern in the cells below the last used cell. The pattern is variable as far as the number of cells below the las used cell it will populate. The pattern is put there by another macro, usin Code ------------------- Interior.Pattern = xlPatternGray ------------------- not conditional formating. I am trying to make the above macro go to the "last used cell in Colum 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 activ cell address should be P16. Any direction appreciated -- Desert Piranh ----------------------------------------------------------------------- Desert Piranha's Profile: http://www.excelforum.com/member.php...fo&userid=2893 View this thread: http://www.excelforum.com/showthread.php?threadid=49111 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Last used cell in column with shading
Hi Dave,
What logic does the other macro use to shade cells? --- Regards, Norman "Desert Piranha" <Desert.Piranha.1zmoqn_1133889009.0547@excelforu m-nospam.com wrote in message news:Desert.Piranha.1zmoqn_1133889009.0547@excelfo rum-nospam.com... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Last used cell in column with shading
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Last used cell in column with shading
Hi Norman, Its the " PseudoBarChart" code that you wrote for me, where it wil populate the cells in a range, with a pattern from the last used row, up to the last used cell i 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" <Desert.Piranha.1zmoqn_1133889009.0547@excelforu m-nospam.com wrote in messag news:Desert.Piranha.1zmoqn_1133889009.0547@excelfo rum-nospam.com... 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 i 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 m 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 Piranh ----------------------------------------------------------------------- Desert Piranha's Profile: http://www.excelforum.com/member.php...fo&userid=2893 View this thread: http://www.excelforum.com/showthread.php?threadid=49111 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Last used cell in column with shading
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" wrote in message news:Desert.Piranha.1zmpwc_1133890504.816@excelfor um-nospam.com... 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" <Desert.Piranha.1zmoqn_1133889009.0547@excelforu m-nospam.com wrote in message news:Desert.Piranha.1zmoqn_1133889009.0547@excelfo rum-nospam.com... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Last used cell in column with shading
Hi Norman, I added line 3 & 4 to the macro, so as to toggle, But on the column it does not go to the cell directly below the pattern/value. Instead it goes way down the colum to a unrelated cell. There are formulas in other columns on the page and i think its goin 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" wrote in messag news:Desert.Piranha.1zmpwc_1133890504.816@excelfor um-nospam.com... 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" <Desert.Piranha.1zmoqn_1133889009.0547@excelforu m-nospam.com wrot in message news:Desert.Piranha.1zmoqn_1133889009.0547@excelfo rum-nospam.com... 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 b a pattern in the cells below the last used cell. The pattern is variable as far as the number of cells below th 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 Piranh ----------------------------------------------------------------------- Desert Piranha's Profile: http://www.excelforum.com/member.php...fo&userid=2893 View this thread: http://www.excelforum.com/showthread.php?threadid=49111 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Last used cell in column with shading
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Last used cell in column with shading
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Last used cell in column with shading
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" <Desert.Piranha.1zmtda_1133895002.6761@excelforu m-nospam.com wrote in message news:Desert.Piranha.1zmtda_1133895002.6761@excelfo rum-nospam.com... 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" wrote in message news:Desert.Piranha.1zmpwc_1133890504.816@excelfor um-nospam.com... 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" <Desert.Piranha.1zmoqn_1133889009.0547@excelforu m-nospam.com wrote in message news:Desert.Piranha.1zmoqn_1133889009.0547@excelfo rum-nospam.com... 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Last used cell in column with shading
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" wrote in message ... 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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Last used cell in column with shading
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" wrote in message ... 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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Last used cell in column with shading
It may appear useless (if not understood), but it will include the shaded
cells of which you speak. -- Regards, Tom Ogivy "Jim Thomlinson" wrote in message ... 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" wrote in message ... 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 |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Last used cell in column with shading
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" wrote in message ... 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" wrote in message ... 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 |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Last used cell in column with shading
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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help w/ cell shading across entire column-when a criteria has been | New Users to Excel | |||
| Format Column Chart Shading | Charts and Charting in Excel | |||
| Conditional Cell Shading (based on the shading of other cells) | Excel Worksheet Functions | |||
| shading a rowwhen a time is entered but no shading when 0 is enter | Excel Worksheet Functions | |||
| How to add shading to more than one column at a time? | New Users to Excel | |||
![Desert Piranha[_9_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
