Excel VBA Highlight search

Question

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

`r.Interior.color`? Is the cell color in blue or the font color? — Siddharth Rout, Apr 12 '16 at 04:40
Also you may want to avoid the use of select. Please see [This](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — Siddharth Rout, Apr 12 '16 at 04:43
I got 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. — user6191327, Apr 12 '16 at 05:09

score 1 · Answer 1 · edited Apr 12 '16 at 05:19

1

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.

edited Apr 12 '16 at 05:19

Siddharth Rout

147,039
17
206
250

answered Apr 12 '16 at 05:10

user6191327

21
2

Good Job on finding the answer yourself :) – Siddharth Rout Apr 12 '16 at 05:20

Excel VBA Highlight search

1 Answers1