CODEWITHSUNDEEP
vbaexcel
user6191327
user6191327

Reputation: 21

Excel VBA Highlight search

I'm trying to use a word in one cell, and then go to another sheet and find that word in another cell. The tricky part is that the second sheet has this word in many places, and I only want the cell that has the word highlighted in blue.

I've tried the following, but the loop keeps on passing over the blue highlighted word and continuing. What am I doing wrong?

Sub TryingIt()

Dim r As Excel.Range
Dim strName As String
Dim strFirstFound As String

strName = ActiveCell.Text

Sheets("Waiting For").Select

Range("A1").Select

Cells.Find(What:=strName, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate

Set r = ActiveCell

If r.Interior.color = vbBlue Then
r.Offset(1, 0).Select
Else:
    Do
    Cells.Find(What:=strName, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate

    Set r = ActiveCell

    Loop While r.Interior.color <> vbBlue

r.Offset(1, 0).Select

End If

End Sub

Upvotes: 1

Views: 33

Answers (1)

user6191327
user6191327

Reputation: 21

Here's the answer:

You have to be careful to be more specific about the color. In my case, it was RGB(0,176,240), not just vbBlue.

Upvotes: 1

Related Questions