Sub Example()
Dim RowsWithDog() As Long, x As Long, SrchTerm As Variant, SrchRange As Range
SrchTerm = "dog"
Set SrchRange = Range("A:A")
RowsWithDog() = FindDoggieRows(SrchRange, SrchTerm, xlPart)
For x = 0 To UBound(RowsWithDog)
'process your rows here
With Range("A" & RowsWithDog(x))
.Characters(InStr(.Value, SrchTerm), Len(CStr(SrchTerm))).Font.FontStyle = "Bold"
.Resize(, 7).Interior.ColorIndex = 6
End With
Next
End Sub
Public Function FindDoggieRows(rng As Range, Value As Variant, LookAt As XlLookAt) As Long()
Dim c As Range, d() As Long, x As Long, firstAddress As String
ReDim d(0)
With rng
Set c = .Find(Value, , xlValues, LookAt)
If Not c Is Nothing Then
d(0) = c.Row
firstAddress = c.Address
Do
Set c = .FindNext(c)
If (Not c Is Nothing) And c.Address <> firstAddress Then
x = x + 1
ReDim Preserve d(x)
d(x) = c.Row
End If
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
FindDoggieRows = d
End Function