I have a process that exports two sets of identical Excel files – a first set where users can edit data and a second reference set that enables file compare to identify updates made in the first set. Each file has only one sheet and the file name is the sheet name plus extension.

I need a macro to loop through both sets of files and highlight the updates in the first (edited) set, but I cannot get the loop through the set of edited files to work. I have tried both While Wend and For Each Next.

Also, the For Each Next loop is giving an Invalid Next control variable reference error that I cannot fix.

Sub Compare_Spreadsheets()
Dim editPath, refPath, FiletoProcess As String
Dim wbk As Variant
Dim wbks As Collection
Dim editwbk, refwbk As Workbook
Dim editws, refws As Worksheet
Dim Folder, File, FSO As Object

editPath = "C:pathXLSX_1"
refPath = "C:pathXLSX_2"

'Loop through files in reference folder
'FiletoProcess = Dir(refPath & "*.xlsx")
'While FiletoProcess <> ""

For Each refwbk In FSO.GetFolder(refPath).Files
    refwbk.Activate

    'Check if file with matching name exists in Edit folder - editwbk.Name = refwbk.Name
    If FSO.FileExists(editPath & refwbk.Name) Then
        Workbooks.Open editwbk
        Else
        MsgBox "File not found"

        For Each refws In refwbk.Worksheets
            For Each editws In editwbk.Worksheets
                If editws.Name = refws.Name Then
                    For Each cell In editws.Range("A1").CurrentRegion
                        If cell.Value <> refws.Range(cell.Address).Value Then
                        cell.Interior.Color = vbYellow
                        MsgBox "Changed value in " & cell.Address & " in sheet " & editws.Name
                        End If
                        Next cell
                    Exit For
                End If
            Next editws
            Exit For
        Exit For
    'End If
Next
End If
Exit For

'Wend

End Sub