I'm trying to write VBA so column AG is filtered to just show "Yes" and then I want to highlight those rows, then clear the autofilter. The problem I'm encountering is if there is no "Yes" in that column, I get an error message. Is it possible to write code that would say if the autofilter on AG = "Yes" then highlight those rows and if not (or if the autofilter on AG = "No" then do nothing? Below is what I have at the moment. Any assistance you can provide is grealty appreciated. Thank you!
With Sheets("Title List")
Sheets("Title List").Select
Columns("AG").Select
Selection.EntireColumn.Hidden = False
lr2 = .Range("A" & Rows.count).End(3).Row
.Range("AG2:AG" & lr2).Formula = "=IFERROR(IF(MATCH('Title List'!AF:AF,'Holdings List'!AF:AF,0),""Yes"",),""No"")"
End With
Application.ScreenUpdating = False
'Add yellow highlighting to owned titles
With Sheets("Title List")
Sheets("Title List").Select
Range("AG2:AG").AutoFilter Field:=33, Criteria1:="<>No"
lr2 = .Range("A" & Rows.count).End(3).Row
.Range("A:AG" & lr2).Select
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
'Clear yellow highlighting from headers
Range("AG:AG").AutoFilter Field:=33
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Columns("AG").Select
Selection.EntireColumn.Hidden = True
Range("A1").Select
With Sheets("Title List")
Sheets("Title List").Select
Columns("AG").Select
Selection.EntireColumn.Hidden = False
lr2 = .Range("A" & Rows.count).End(3).Row
.Range("AG2:AG" & lr2).Formula = "=IFERROR(IF(MATCH('Title List'!AF:AF,'Holdings List'!AF:AF,0),""Yes"",),""No"")"
End With
Application.ScreenUpdating = False
'Add yellow highlighting to owned titles
With Sheets("Title List")
Sheets("Title List").Select
Range("AG2:AG").AutoFilter Field:=33, Criteria1:="<>No"
lr2 = .Range("A" & Rows.count).End(3).Row
.Range("A:AG" & lr2).Select
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
'Clear yellow highlighting from headers
Range("AG:AG").AutoFilter Field:=33
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Columns("AG").Select
Selection.EntireColumn.Hidden = True
Range("A1").Select