Loop to find and mark with color does not stop - not sure why

user_11689318 0
Viewed 21

I have a simple piece of code - when I run it separately it works and ends normally, however when I run it along with a some other code (they do not refer to each other at any point) the macro is still working in the background - from what I see on the screen the break occurs on this task - not sure why, any tips for a beginner ?

With Worksheets(1).Range("a1:a500")
    Set c = .Find("Urgent", LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address            
    Do          
        c.Interior.Color = RGB(255, 0, 0)
        Set c = .FindNext(c)
        Loop While Not c Is Nothing
    End If
End With
vba excel
1 Answers

The condition of the Do-Loop should be like this - Loop While c.Address <> firstAddress

Otherwise, the .FindNext(c) would start repeating the cells, which were already looped through:

Sub Test()
    Dim c as Range
    Dim firstAddress as String

    With Worksheets(1).Range("A1:A500")            
        Set c = .Find("Urgent", LookIn:=xlValues)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                c.Interior.Color = RGB(255, 0, 0)
                Set c = .FindNext(c)
            Loop While c.Address <> firstAddress
        End If
    End With

End Sub
user_54486260
Related