macro not working !!!

[paquirl]

this macro is supposed to highlight rows that meet certain criteria in one of three columns. it is not highlighting all the rows in the third column.

Sub paquirl3()
' ABSOLUTEHIGHLIGHTED Macro
' Macro recorded 3/10/2011 by Andrew Quirl
'
' Keyboard Shortcut: Ctrl+Shift+U
'
Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
Rows("4:4").Select
Selection.AutoFilter
Selection.AutoFilter Field:=7, Criteria1:=">=5 to 6 weeks", Operator:= _
xlAnd
Range("A5:A" & lr).EntireRow.SpecialCells(xlCellTypeVisible).Interior.ColorIndex = 6
Range("A5:A" & lr).EntireRow.SpecialCells(xlCellTypeVisible).Interior.Pattern = xlSolid
Selection.AutoFilter Field:=7
Selection.AutoFilter Field:=25, Criteria1:=">(35) Active", Operator:= _
xlAnd
Range("A405:A" & lr).EntireRow.SpecialCells(xlCellTypeVisible).Interior.ColorIndex = 6
Range("A405:A" & lr).EntireRow.SpecialCells(xlCellTypeVisible).Interior.Pattern = xlSolid
Selection.AutoFilter Field:=25
Selection.AutoFilter Field:=38, Criteria1:="Yes"
Range("A1026:A" & lr).EntireRow.SpecialCells(xlCellTypeVisible).Interior.ColorIndex = 6
Range("A1026:A" & lr).EntireRow.SpecialCells(xlCellTypeVisible).Interior.Pattern = xlSolid
Selection.AutoFilter Field:=38
End Sub

 

[AlphaFrog]

Do you mean it's not highlighting all the "Yes" rows?

Maybe try changing this...

lr = Cells(Rows.Count, 1).End(xlUp).Row

Not always the most reliable method to get the last used row depending on how your data is structured. If column A doesn't have an entry in the last used row then it may not be accurate.


To this...

lr = Cells.Find("*", [A1], xlFormulas, , xlByRows, xlPrevious).Row

Also, your code will only highlight "Yes" rows within rows 1026 to Lr

 

[paquirl]

i made the change and it is still missing a bunch of the "yes" rows.

additionally, i'm wondering if i need to also adjust something to make it know to highlight ALL the rows that show up when a filter is applied. this is because the report is different from week to week. one week the report may be quite long and another week it may be short. if i recorded the macro when it was short, it may not highlight all the rows it needs to when the report has more data on it. what do you think?

 

[AlphaFrog]

Do you want to highlight "Yes" rows above row 1026 as well?

 

[paquirl]

well what i'm trying to do with this macro is:

1. autofilter all the columns
2. choose a column and filter by criteria chosen >=5 to 6 weeks
3. highlight all rows that meet this criteria
4. unfilter this column to "show all" again
5. filter another column by criteria chosen >(35) Active
6. highlight all rows that meet this criteria
7. unfilter this column to "show all" again
8. filter another column by criteria chosen YES
9. highlight all rows that meet this criterion
10. unfilter this column to "show all" again

 

[AlphaFrog]

Change all the A405's and A1026's to A5

 

[paquirl]

i think you did it - you rock!

would you give me your email to keep in my contact list?

-Andrew

 

[AlphaFrog]

Glad it worked but no on the email.