Dim cell As Range
For Each cell In Range("a3:a250")
If InStr(cell, "Trex") Then
cell.Offset(0, 0).EntireRow.Select
End If
Next
Above works but now I want to make it highlight in addition to the row
highlighted all rows afterwards until the next time it finds "Trex" or 5
consecutive empty cells.

Sub dDD()
Dim cell As Range
Dim bFound As Boolean
Dim cnt As Long
Dim r As Range
bFound = False
cnt = 0
For Each cell In Range("a3:a250")
If InStr(cell, "Trex") Then
If Not bFound Then
Set r = cell
bFound = True
cnt = 0
Else
Range(r, cell).EntireRow.Select
Exit Sub
End If
End If
If IsEmpty(cell) Then
cnt = cnt + 1
If cnt = 5 And bFound Then
Range(r, cell.Offset(-5, 0)).EntireRow.Select
Exit Sub
End If
Else
cnt = 0
End If
Next
End Sub

--
Regards,
Tom Ogilvy


"JV" wrote:

Dim cell As Range
For Each cell In Range("a3:a250")
If InStr(cell, "Trex") Then
cell.Offset(0, 0).EntireRow.Select
End If
Next
Above works but now I want to make it highlight in addition to the row
highlighted all rows afterwards until the next time it finds "Trex" or 5
consecutive empty cells.