VBA: How can I make my sub-procedure dynamic to loop through a range?

[jerradgarrett]

I have a log that keeps track of what documents my employees have read. When they have read one in person, I manually change the color of the cell in my workbook. Then I have a macro that will look for any cells with the same document name as its value and will match the color. I want to be able to do this with all documents without having to write this macro for every document. I have tried for loops and do loops but I don't fully understand how they work.

The first sub procedure Match1 works perfectly fine for one document within a worksheet. MatchAll is where I had problems.

I get "Run-time error '1004': Method 'Range' of object '_Global' failed" a lot or "object variable or with block variable not set"

Public Sub Match1()

    Dim c As Range
    Dim firstAddress As String
    
    firstAddress = Range("A10").Value

    With Range("C1:K500")
        Set c = .Find(firstAddress, LookIn:=xlValues)
        If Not c.Interior.Color = Range("A10").Interior.Color Then
            firstAddress = c.Address
            Do
                c.Interior.Color = Replace(firstAddress, firstAddress, Range("A10").Interior.Color)
                Set c = .FindNext(c)
            Loop While Not c.Interior.Color = Range("A10").Interior.Color
        End If
    End With

End Sub

Public Sub MatchAll()

Dim a As Range
Dim i As Integer
Dim c As Range
Dim firstAddress As String

 
For i = 8 To 197

a = Range(1, i)

firstAddress = a.Value

        With Range("C1:K500")
            Set c = .Find(firstAddress, LookIn:=xlValues)
            If Not c.Interior.Color = a.Interior.Color Then
            firstAddress = c.Address
                Do
                    c.Interior.Color = Replace(firstAddress, firstAddress, a.Interior.Color)
                    Set c = .FindNext(c)
                Loop While Not c.Interior.Color = a.Interior.Color
         End If
        End With
        
Next i

End Sub

 

[jerradgarrett]

Is there any possibility that some of the values you are searching for won't be found?
If so, have you tested that scenario out?

Yeah, within Range("A8:A197") there are cells that are just headers to sort the documents and are not in Range("C1:K500") . Even with this the code still worked for me.

 

[Joe4]

OK, if it is accounted for and all works out for you, that is all that matters.