UsedRange in all worksheets Wb1 to find matching value in Wb2.

[BRB1983]

I'm trying to get workbook 1, all sheets used range, if cell value = a cell value in column a of sheets"Final" in Wb2. if so color cell indexcolor 6. the following cod runs but no color change.

Sub color_matching_between_workbooks()
    Dim sh As Worksheet
    'Dim wb1 As Workbook
    Dim wb2 As Workbook
    Set wb2 = Workbooks.Open("C:\Users\Desktop\17057 System Line List.xlsm")
    Dim finalrow As Integer
    Dim i As Integer
    finalrow = wb2.Sheets("Final").Range("A6000").End(xlUp).Row
    For i = 2 To finalrow
    
    
    For Each sh In ThisWorkbook.Worksheets
Dim cell As Range


For Each cell In sh.usedrange
If cell.Value = i Then
cell.Interior.ColorIndex = 4
End If


Next cell
Next sh
    
    Next i
End Sub

 

[mumps]

If the blank cells are the only problem, give this a try:

Sub color_matching_between_workbooks()
    Application.ScreenUpdating = False
    Dim sh As Worksheet, srcWS As Worksheet, wb1 As Workbook, wb2 As Workbook, rng As Range, fnd As Range
    Set wb1 = ThisWorkbook
    Set wb2 = Workbooks.Open("C:\Users\Desktop\17057 System Line List.xlsm")
    Set srcWS = Sheets("Final")
    For Each sh In wb1.Sheets
        sh.UsedRange.Cells.Interior.ColorIndex = xlNone
        For Each rng In sh.UsedRange
            If rng <> "" Then
                Set fnd = srcWS.Range("A:A").Find(rng, LookIn:=xlValues, lookat:=xlWhole)
                If Not fnd Is Nothing Then
                    rng.Interior.ColorIndex = 6
                End If
            End If
        Next rng
    Next sh
    Application.ScreenUpdating = True
End Sub

 

[BRB1983]

 

[BRB1983]

that solved the issue, simple fix, tanks again.

 

[mumps]

My pleasure.