Dynamic Autofilter using VBA
 

Hello,

I'm trying to use vba to color-code cells A through M in the visible rows that are chosen based on an autofilter in column M. I was able to do this using the following code which autofilters for the criteria selected in column M and then it scrolls to the first visible cell in the data that I want to color code(in this case cell A345) and then it highlights the visible data and color codes it.

Not sure, though, how to do it dynamically because each month the total number of rows will change (this month , there were 2607) and also the number for records for the selected criteria will change (in this example, the first record with the criteria = "Replaced" was on row 345, but it could be on row 245 next month).

ActiveSheet.Range("$A$1:$Y$2607").AutoFilter Field:=13, Criteria1:= _
"Replaced"
ActiveWindow.ScrollRow = 2585
ActiveWindow.ScrollRow = 337
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A345:M345").Select
Range(Selection, Selection.End(xlDown)).Select
With Selection.Font
.ThemeColor = xlThemeColorDark2
.TintAndShade = -0.499984740745262
End With

Thanks,
Pat

Dynamic Autofilter using VBA
 

Hi Pat,

Am Fri, 3 Apr 2015 17:50:28 -0700 (PDT) schrieb PatJohnson:

I'm trying to use vba to color-code cells A through M in the visible rows that are chosen based on an autofilter in column M. I was able to do this using the following code which autofilters for the criteria selected in column M and then it scrolls to the first visible cell in the data that I want to color code(in this case cell A345) and then it highlights the visible data and color codes it.

Not sure, though, how to do it dynamically because each month the total number of rows will change (this month , there were 2607) and also the number for records for the selected criteria will change (in this example, the first record with the criteria = "Replaced" was on row 345, but it could be on row 245 next month).

try:

Sub Test()
Dim LRow As Long

With ActiveSheet
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
.Range("$A$1:$Y$" & LRow).AutoFilter Field:=13, _
Criteria1:="Replaced"
With .Range("$A$2:$Y$" & LRow) _
.SpecialCells(xlCellTypeVisible).Font
.ThemeColor = xlThemeColorDark2
.TintAndShade = -0.499984740745262
End With
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Dynamic Autofilter using VBA
 

On Saturday, April 4, 2015 at 12:47:35 AM UTC-7, Claus Busch wrote:
Hi Pat,

Am Fri, 3 Apr 2015 17:50:28 -0700 (PDT) schrieb PatJohnson:

I'm trying to use vba to color-code cells A through M in the visible rows that are chosen based on an autofilter in column M. I was able to do this using the following code which autofilters for the criteria selected in column M and then it scrolls to the first visible cell in the data that I want to color code(in this case cell A345) and then it highlights the visible data and color codes it.

Not sure, though, how to do it dynamically because each month the total number of rows will change (this month , there were 2607) and also the number for records for the selected criteria will change (in this example, the first record with the criteria = "Replaced" was on row 345, but it could be on row 245 next month).

try:

Sub Test()
Dim LRow As Long

With ActiveSheet
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
.Range("$A$1:$Y$" & LRow).AutoFilter Field:=13, _
Criteria1:="Replaced"
With .Range("$A$2:$Y$" & LRow) _
.SpecialCells(xlCellTypeVisible).Font
.ThemeColor = xlThemeColorDark2
.TintAndShade = -0.499984740745262
End With
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Thanks Claus! Worked like a charm.