FIND Command

[Neil Shaw]

Hi

Im using the find command and the findnext. It keeps going in a loop
so when it has gone to the bottom of the range it then goes back to
the top. How can i stops this basically i want to look in a column for
cells with PC in and then set the background color of the row

Thanks Neil

 

[Don Guillett]

As always, post your coding efforts for comments.

 

[Stuart]

capture the address of the first find, then test subsequent finds for the
same address

 

[Mike Q.]

I am new at this stuff myself but if I understand your
question this might help. It is case sensitive and will
highlight from column A to eterity. I hope you can fix it
right.

Option Explicit

Sub PCYELLOW()

Dim FoundCell As Range
Dim firstAddress As String
Dim myWord As String
Dim YellowCell As Range

myWord = "PC"
With ActiveSheet.Cells
Set FoundCell = Nothing
Set FoundCell = Cells.Find(What:=myWord,
LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=True)

If Not FoundCell Is Nothing Then
firstAddress = FoundCell.Address

Do
If YellowCell Is Nothing Then
Set YellowCell = FoundCell
Else: Set YellowCell = Union(FoundCell,
YellowCell)
End If
Set FoundCell = .FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address <> firstAddress
End If
End With

If YellowCell Is Nothing Then Exit Sub
YellowCell.EntireRow.Interior.ColorIndex = 6
YellowCell.EntireRow.Interior.Pattern = xlSolid
End Sub