czeknere
06-01-2015, 08:55 AM
Working on a small macro that will allow a user to find a given list of words throughout an excel document. If the word is found the whole row is highlighted in red. It works, but for some reason it highlights a bunch of empty cells after the cells containing information. Code is below:
Sub DeleteExclusions()
Dim SearchString
Dim i As Long, j As Long, calc As Long
With Application
.DisplayAlerts = False
.ScreenUpdating = False
.EnableEvents = False
calc = .Calculation
.Calculation = xlCalculationManual
End With
SearchString = Array("Term1", "Term2", "Term3", "Term4")
With ActiveSheet
.AutoFilterMode = False
For j = 3 To 4 '3 = Col C and 4 = Col D
For i = LBound(SearchString) To UBound(SearchString)
.Cells(1).AutoFilter Field:=j, Criteria1:="=*" & SearchString(i) & "*"
If .AutoFilter.Range.Rows.Count > 1 Then
.UsedRange.Columns(1).Offset(1).SpecialCells(12).EntireRow.Interior.ColorIn dex = 3
End If
Next i
.AutoFilterMode = False
Next j
End With
With Application
.EnableEvents = False
.Calculation = calc
End With
End Sub
I had tried to add a section after the main part that would then go back and find any blank cells and turn them back to white. I know it's not ideal, but it was a work around. However, when my coworker runs the macro she gets a Runtime 1004 - "autofilter method of range class failed" so I'd like to avoid going this route if possible.
Hopefully this is enough information. Thanks in advance for the help!
Sub DeleteExclusions()
Dim SearchString
Dim i As Long, j As Long, calc As Long
With Application
.DisplayAlerts = False
.ScreenUpdating = False
.EnableEvents = False
calc = .Calculation
.Calculation = xlCalculationManual
End With
SearchString = Array("Term1", "Term2", "Term3", "Term4")
With ActiveSheet
.AutoFilterMode = False
For j = 3 To 4 '3 = Col C and 4 = Col D
For i = LBound(SearchString) To UBound(SearchString)
.Cells(1).AutoFilter Field:=j, Criteria1:="=*" & SearchString(i) & "*"
If .AutoFilter.Range.Rows.Count > 1 Then
.UsedRange.Columns(1).Offset(1).SpecialCells(12).EntireRow.Interior.ColorIn dex = 3
End If
Next i
.AutoFilterMode = False
Next j
End With
With Application
.EnableEvents = False
.Calculation = calc
End With
End Sub
I had tried to add a section after the main part that would then go back and find any blank cells and turn them back to white. I know it's not ideal, but it was a work around. However, when my coworker runs the macro she gets a Runtime 1004 - "autofilter method of range class failed" so I'd like to avoid going this route if possible.
Hopefully this is enough information. Thanks in advance for the help!