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]

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
        For Each rng In sh.UsedRange
            Set fnd = srcWS.Range("A:A").Find(rng, LookIn:=xlValues, lookat:=xlWhole)
            If Not fnd Is Nothing Then
                rng.Interior.ColorIndex = 6
            End If
        Next rng
    Next sh
    Application.ScreenUpdating = True
End Sub

 

[BRB1983]

it works like a charm, thank you very much!

 

[mumps]

You are very welcome.

 

[BRB1983]

Mumps, ran into small issue, notice that it colors some random ranges. i double check that the cells are clear. i can not figure it out.

 

[BRB1983]

 

[mumps]

Try this version of the macro. It will clear any existing color fill before starting the search each time you run the macro.

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
            Set fnd = srcWS.Range("A:A").Find(rng, LookIn:=xlValues, lookat:=xlWhole)
            If Not fnd Is Nothing Then
                rng.Interior.ColorIndex = 6
            End If
        Next rng
    Next sh
    Application.ScreenUpdating = True
End Sub

 

[BRB1983]

still the same issue. So this may help. i have 30 tabs that this code look at. so on the first tab is where it is coloring in unused cells. ("A1:N54") the is nothing in these cells. i cleared the cells to be sure. and the funny part is the longer the code runs, the wider the range gets. color sets in at end of code.

 

[mumps]

Are you saying that the problem is that the macro is coloring blank cells otherwise it works properly?

 

[BRB1983]

yes, i would post a screen shot but cant figure out how.