Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with highlight code
I am having a problem, this code is supposed to select using
autofilter a blank in column 29 and an "X" in column 34, which it does. The problem is that sometimes there are no row values left after these 2 autofilter conditions and it puts a value of 3 and highlights all the ROWS outside of the .autofilter condition and I just want it to do that on the rows that survive and not rows that are outside of these 2 conditions in the worksheet. How do I tell this code to only highlight the color and the value of 3 into rows that survive the autofilter seletions or skip it if there are no values ? Thank you. With Range("A6:AH" & lr) On Error Resume Next .AutoFilter Field:=29, Criteria1:="=", Operator:=xlAnd .AutoFilter Field:=34, Criteria1:="X" .Sort Key1:=Range("C7"), Order1:=xlAscending, Key2:=Range( _ "D7"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _ :=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _ DataOption2:=xlSortNormal Range("AI7:AI" & lr).Value = 3 Range("A7:AH" & lr).Select With Selection.Interior .ColorIndex = 36 ' light yellow .Pattern = xlSolid End With .AutoFilter End With |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with highlight code
So you're filtering A:AH and sorting A:AH, but shading A:AI?
I would think that if you had data in AI that you would want to filter and sort that so that it would be associated with the correct row after the sort. But I think that this does what you asked: Option Explicit Sub testme() Dim wks As Worksheet Dim lr As Long Set wks = Worksheets("Sheet1") With wks 'remove any existing arrows and filters! .AutoFilterMode = False 'however you set this lr variable lr = .Cells(.Rows.Count, "A").End(xlUp).Row With .Range("A6:AH" & lr) .AutoFilter Field:=29, Criteria1:="=", Operator:=xlAnd .AutoFilter Field:=34, Criteria1:="X" End With With .AutoFilter.Range If .Columns(1).Cells.SpecialCells(xlCellTypeVisible) _ .Cells.Count = 1 Then 'only the header row is visible, do nothing! Else .Sort _ Key1:=.Columns(3), Order1:=xlAscending, _ Key2:=.Columns(4), Order2:=xlAscending, _ Header:=xlYes, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal, _ DataOption2:=xlSortNormal With .Resize(.Rows.Count - 1, .Columns.Count + 1).Offset(1, 0) _ .Cells.SpecialCells(xlCellTypeVisible).Interior .ColorIndex = 36 .Pattern = xlSolid End With End If End With .AutoFilterMode = False End With End Sub If you wanted to filter, sort and shade A:AI, then try this: Option Explicit Sub testme2() Dim wks As Worksheet Dim lr As Long Set wks = Worksheets("Sheet1") With wks 'remove any existing arrows and filters! .AutoFilterMode = False 'however you set this lr variable lr = .Cells(.Rows.Count, "A").End(xlUp).Row With .Range("A6:AI" & lr) .AutoFilter Field:=29, Criteria1:="=", Operator:=xlAnd .AutoFilter Field:=34, Criteria1:="X" End With With .AutoFilter.Range If .Columns(1).Cells.SpecialCells(xlCellTypeVisible) _ .Cells.Count = 1 Then 'only the header row is visible, do nothing! Else .Sort _ Key1:=.Columns(3), Order1:=xlAscending, _ Key2:=.Columns(4), Order2:=xlAscending, _ Header:=xlYes, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal, _ DataOption2:=xlSortNormal With .Resize(.Rows.Count - 1).Offset(1, 0) _ .Cells.SpecialCells(xlCellTypeVisible).Interior .ColorIndex = 36 .Pattern = xlSolid End With End If End With .AutoFilterMode = False End With End Sub ======= The important lines are these: With .AutoFilter.Range If .Columns(1).Cells.SpecialCells(xlCellTypeVisible) .Cells.Count = 1 Then and With .Resize(.Rows.Count - 1, .Columns.Count + 1).Offset(1, 0) _ .Cells.SpecialCells(xlCellTypeVisible).Interior The first looks at the entire range that was just filtered. Then it looks at the first column of that range and counts the visible cells in that column. If it's equal to 1, then only the headers are visible. The second line looks at filtered range, but then resizes it by one less row -- to avoid the header and one more column (AI). But then it offsets that range by 1 row and 0 columns (just the detail records). Then it only looks at the visible cells in that range. The second version doesn't need to add a column. On 08/12/2010 11:40, RompStar wrote: I am having a problem, this code is supposed to select using autofilter a blank in column 29 and an "X" in column 34, which it does. The problem is that sometimes there are no row values left after these 2 autofilter conditions and it puts a value of 3 and highlights all the ROWS outside of the .autofilter condition and I just want it to do that on the rows that survive and not rows that are outside of these 2 conditions in the worksheet. How do I tell this code to only highlight the color and the value of 3 into rows that survive the autofilter seletions or skip it if there are no values ? Thank you. With Range("A6:AH"& lr) On Error Resume Next .AutoFilter Field:=29, Criteria1:="=", Operator:=xlAnd .AutoFilter Field:=34, Criteria1:="X" .Sort Key1:=Range("C7"), Order1:=xlAscending, Key2:=Range( _ "D7"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _ :=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _ DataOption2:=xlSortNormal Range("AI7:AI"& lr).Value = 3 Range("A7:AH"& lr).Select With Selection.Interior .ColorIndex = 36 ' light yellow .Pattern = xlSolid End With .AutoFilter End With -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with highlight code
Thanks again Dave for helping me out, I will look at your code with
great interest! Thank you again for taking time! I am learning a lot from you. On Aug 12, 10:34*am, Dave Peterson wrote: So you're filtering A:AH and sorting A:AH, but shading A:AI? I would think that if you had data in AI that you would want to filter and sort that so that it would be associated with the correct row after the sort. But I think that this does what you asked: Option Explicit Sub testme() * * *Dim wks As Worksheet * * *Dim lr As Long * * *Set wks = Worksheets("Sheet1") * * *With wks * * * * *'remove any existing arrows and filters! * * * * *.AutoFilterMode = False * * * * *'however you set this lr variable * * * * *lr = .Cells(.Rows.Count, "A").End(xlUp).Row * * * * *With .Range("A6:AH" & lr) * * * * * * *.AutoFilter Field:=29, Criteria1:="=", Operator:=xlAnd * * * * * * *.AutoFilter Field:=34, Criteria1:="X" * * * * *End With * * * * *With .AutoFilter.Range * * * * * * *If .Columns(1).Cells.SpecialCells(xlCellTypeVisible) _ * * * * * * * * * .Cells.Count = 1 Then * * * * * * * * *'only the header row is visible, do nothing! * * * * * * *Else * * * * * * * * *.Sort _ * * * * * * * * * * *Key1:=.Columns(3), Order1:=xlAscending, _ * * * * * * * * * * *Key2:=.Columns(4), Order2:=xlAscending, _ * * * * * * * * * * *Header:=xlYes, _ * * * * * * * * * * *OrderCustom:=1, _ * * * * * * * * * * *MatchCase:=False, _ * * * * * * * * * * *Orientation:=xlTopToBottom, _ * * * * * * * * * * *DataOption1:=xlSortNormal, _ * * * * * * * * * * *DataOption2:=xlSortNormal * * * * * * * * *With .Resize(.Rows.Count - 1, .Columns..Count + 1).Offset(1, 0) _ * * * * * * * * * * * * *.Cells.SpecialCells(xlCellTypeVisible).Interior * * * * * * * * * * *.ColorIndex = 36 * * * * * * * * * * *.Pattern = xlSolid * * * * * * * * *End With * * * * * * *End If * * * * *End With * * * * *.AutoFilterMode = False * * *End With End Sub If you wanted to filter, sort and shade A:AI, then try this: Option Explicit Sub testme2() * * *Dim wks As Worksheet * * *Dim lr As Long * * *Set wks = Worksheets("Sheet1") * * *With wks * * * * *'remove any existing arrows and filters! * * * * *.AutoFilterMode = False * * * * *'however you set this lr variable * * * * *lr = .Cells(.Rows.Count, "A").End(xlUp).Row * * * * *With .Range("A6:AI" & lr) * * * * * * *.AutoFilter Field:=29, Criteria1:="=", Operator:=xlAnd * * * * * * *.AutoFilter Field:=34, Criteria1:="X" * * * * *End With * * * * *With .AutoFilter.Range * * * * * * *If .Columns(1).Cells.SpecialCells(xlCellTypeVisible) _ * * * * * * * * * .Cells.Count = 1 Then * * * * * * * * *'only the header row is visible, do nothing! * * * * * * *Else * * * * * * * * *.Sort _ * * * * * * * * * * *Key1:=.Columns(3), Order1:=xlAscending, _ * * * * * * * * * * *Key2:=.Columns(4), Order2:=xlAscending, _ * * * * * * * * * * *Header:=xlYes, _ * * * * * * * * * * *OrderCustom:=1, _ * * * * * * * * * * *MatchCase:=False, _ * * * * * * * * * * *Orientation:=xlTopToBottom, _ * * * * * * * * * * *DataOption1:=xlSortNormal, _ * * * * * * * * * * *DataOption2:=xlSortNormal * * * * * * * * *With .Resize(.Rows.Count - 1).Offset(1, 0) _ * * * * * * * * * * * * *.Cells.SpecialCells(xlCellTypeVisible).Interior * * * * * * * * * * *.ColorIndex = 36 * * * * * * * * * * *.Pattern = xlSolid * * * * * * * * *End With * * * * * * *End If * * * * *End With * * * * *.AutoFilterMode = False * * *End With End Sub ======= The important lines are these: * * * * *With .AutoFilter.Range * * * * * * *If .Columns(1).Cells.SpecialCells(xlCellTypeVisible) * * * * * * * * * .Cells.Count = 1 Then and * * * * * * * * *With .Resize(.Rows.Count - 1, .Columns..Count + 1).Offset(1, 0) _ * * * * * * * * * * * * *.Cells.SpecialCells(xlCellTypeVisible).Interior The first looks at the entire range that was just filtered. *Then it looks at the first column of that range and counts the visible cells in that column. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with highlight code
Got a question, my headers start at row 6, because I have other
formulas that measure things above there, so the first row with data would be row 7. I acctually have several of these sorts, what I posted is just one of them and I need to put into colum AI with each different .autofilter sort 1, 2, 3, 4, so that at the end I can sort on this helper column and all my sorts will be in the right sequence. I noticed that everything is offset by a few rows, how would I fix that ? Thanks! Sorry for not mentioning that in the original post. On Aug 12, 10:48*am, RompStar wrote: Thanks again Dave for helping me out, I will look at your code with great interest! Thank you again for taking time! *I am learning a lot from you. On Aug 12, 10:34*am, Dave Peterson wrote: So you're filtering A:AH and sorting A:AH, but shading A:AI? I would think that if you had data in AI that you would want to filter and sort that so that it would be associated with the correct row after the sort.. But I think that this does what you asked: Option Explicit Sub testme() * * *Dim wks As Worksheet * * *Dim lr As Long * * *Set wks = Worksheets("Sheet1") * * *With wks * * * * *'remove any existing arrows and filters! * * * * *.AutoFilterMode = False * * * * *'however you set this lr variable * * * * *lr = .Cells(.Rows.Count, "A").End(xlUp).Row * * * * *With .Range("A6:AH" & lr) * * * * * * *.AutoFilter Field:=29, Criteria1:="=", Operator:=xlAnd * * * * * * *.AutoFilter Field:=34, Criteria1:="X" * * * * *End With * * * * *With .AutoFilter.Range * * * * * * *If .Columns(1).Cells.SpecialCells(xlCellTypeVisible) _ * * * * * * * * * .Cells.Count = 1 Then * * * * * * * * *'only the header row is visible, do nothing! * * * * * * *Else * * * * * * * * *.Sort _ * * * * * * * * * * *Key1:=.Columns(3), Order1:=xlAscending, _ * * * * * * * * * * *Key2:=.Columns(4), Order2:=xlAscending, _ * * * * * * * * * * *Header:=xlYes, _ * * * * * * * * * * *OrderCustom:=1, _ * * * * * * * * * * *MatchCase:=False, _ * * * * * * * * * * *Orientation:=xlTopToBottom, _ * * * * * * * * * * *DataOption1:=xlSortNormal, _ * * * * * * * * * * *DataOption2:=xlSortNormal * * * * * * * * *With .Resize(.Rows.Count - 1, .Columns.Count + 1).Offset(1, 0) _ * * * * * * * * * * * * *.Cells.SpecialCells(xlCellTypeVisible).Interior * * * * * * * * * * *.ColorIndex = 36 * * * * * * * * * * *.Pattern = xlSolid * * * * * * * * *End With * * * * * * *End If * * * * *End With * * * * *.AutoFilterMode = False * * *End With End Sub If you wanted to filter, sort and shade A:AI, then try this: Option Explicit Sub testme2() * * *Dim wks As Worksheet * * *Dim lr As Long * * *Set wks = Worksheets("Sheet1") * * *With wks * * * * *'remove any existing arrows and filters! * * * * *.AutoFilterMode = False * * * * *'however you set this lr variable * * * * *lr = .Cells(.Rows.Count, "A").End(xlUp).Row * * * * *With .Range("A6:AI" & lr) * * * * * * *.AutoFilter Field:=29, Criteria1:="=", Operator:=xlAnd * * * * * * *.AutoFilter Field:=34, Criteria1:="X" * * * * *End With * * * * *With .AutoFilter.Range * * * * * * *If .Columns(1).Cells.SpecialCells(xlCellTypeVisible) _ * * * * * * * * * .Cells.Count = 1 Then * * * * * * * * *'only the header row is visible, do nothing! * * * * * * *Else * * * * * * * * *.Sort _ * * * * * * * * * * *Key1:=.Columns(3), Order1:=xlAscending, _ * * * * * * * * * * *Key2:=.Columns(4), Order2:=xlAscending, _ * * * * * * * * * * *Header:=xlYes, _ * * * * * * * * * * *OrderCustom:=1, _ * * * * * * * * * * *MatchCase:=False, _ * * * * * * * * * * *Orientation:=xlTopToBottom, _ * * * * * * * * * * *DataOption1:=xlSortNormal, _ * * * * * * * * * * *DataOption2:=xlSortNormal * * * * * * * * *With .Resize(.Rows.Count - 1).Offset(1, 0) _ * * * * * * * * * * * * *.Cells.SpecialCells(xlCellTypeVisible).Interior * * * * * * * * * * *.ColorIndex = 36 * * * * * * * * * * *.Pattern = xlSolid * * * * * * * * *End With * * * * * * *End If * * * * *End With * * * * *.AutoFilterMode = False * * *End With End Sub ======= The important lines are these: * * * * *With .AutoFilter.Range * * * * * * *If .Columns(1).Cells.SpecialCells(xlCellTypeVisible) * * * * * * * * * .Cells.Count = 1 Then and * * * * * * * * *With .Resize(.Rows.Count - 1, .Columns.Count + 1).Offset(1, 0) _ * * * * * * * * * * * * *.Cells.SpecialCells(xlCellTypeVisible).Interior The first looks at the entire range that was just filtered. *Then it looks at the first column of that range and counts the visible cells in that column. If it's equal to 1, then only the headers are visible. The second line looks at filtered range, but then resizes it by one less row -- to avoid the header and one more column (AI). *But then it offsets that range by 1 row and 0 columns (just the detail records). *Then it only looks at the visible cells in that range. The second version doesn't need to add a column. On 08/12/2010 11:40, RompStar wrote: I am having a problem, this code is supposed to select using autofilter a blank in column 29 and an "X" in column 34, which it does. *The problem is that sometimes there are no row values left after these 2 autofilter conditions and it puts a value of 3 and highlights all the ROWS outside of the .autofilter condition and I just want it to do that on the rows that survive and not rows that are outside of these 2 conditions in the worksheet. How do I tell this code to only highlight the color and the value of 3 into rows that survive the autofilter seletions or skip it if there are no values ? Thank you. With Range("A6:AH"& *lr) * * * On Error Resume Next * * *.AutoFilter Field:=29, Criteria1:="=", Operator:=xlAnd * * *.AutoFilter Field:=34, Criteria1:="X" * * *.Sort Key1:=Range("C7"), Order1:=xlAscending, Key2:=Range( _ * * * * *"D7"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _ * * * * *:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _ * * * * *DataOption2:=xlSortNormal * * *Range("AI7:AI"& *lr).Value = 3 * * *Range("A7:AH"& *lr).Select * * *With Selection.Interior * * * * *.ColorIndex = 36 * * * ' light yellow * * * * *.Pattern = xlSolid * * *End With * * *.AutoFilter End With -- Dave Peterson- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with highlight code
I figured that out, thanks for the help!
Saved me a lot of headaches. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Code to highlight or bold subtotals if not a certain value | Excel Programming | |||
Auto Highlight Code | Excel Discussion (Misc queries) | |||
code to highlight max value in a range | Excel Programming | |||
Highlight Track Changes with VBA Code? | Excel Programming |