Compare 2 workbooks to highlight unknown

[turtleKing]

So I have one 'master sheet' and 'data sheet'. I need to find and highlight the names that are found in the data sheet that is not found in the master sheet.
Currently this is my code

Sub Generate_Counts()

Dim Ws1 As Worksheet
    Dim Ws1 As Worksheet
    Dim Ws2 As Worksheet
    Set Ws1 = Workbooks("Close_2022").Worksheets("Sheet 1")
    Set Ws2 = Workbooks("User_ID").Worksheets("Sheet 2")

    
    Dim LR1, LR2, LR3 As Integer
    LR1 = Ws1.Cells(Ws1.Rows.Count, 3).End(xlUp).Row
    LR2 = Ws2.Cells(Ws2.Rows.Count, 1).End(xlUp).Row
    
    Dim Rng1 As Range
    Dim Rng2 As Range
    Set Rng1 = Ws1.Range("C2:C" & LR1)
    Set Rng2 = Ws2.Range("A2:A" & LR2)
    
    Dim x, y As Integer
    Dim Find As Variant
        For x = 1 To LR2
            For y = 2 To LR1
            
            If Ws2.Cells(x, 1).Values <> Ws1.Cells(y, 3).Values Then
            Ws1.Cells(y, 3).Interior.Color = vbYellow
            End If
        Next y
    Next x
End Sub

The error is 'object does not support this property or method

 

[Alex Blakenburg]

You need to provide which row is highlighted in the code as well as the error message.
Having said that it is more than likely the line below which uses the term "Values" this should be "Value".
If Ws2.Cells(x, 1).Values <> Ws1.Cells(y, 3).Values Then

Note: Using a dictionary will be faster but will take more code, so see how you go with what you have first.

 

[turtleKing]

You need to provide which row is highlighted in the code as well as the error message.
Having said that it is more than likely the line below which uses the term "Values" this should be "Value".
If Ws2.Cells(x, 1).Values <> Ws1.Cells(y, 3).Values Then

Note: Using a dictionary will be faster but will take more code, so see how you go with what you have first.

alright, i have changed values to value and somehow it just highlights the entire column of Ws1.Cells(y, 3).Values

 

[Alex Blakenburg]

OK give this a try:

Sub Generate_Counts()

    Dim Ws1 As Worksheet
    Dim Ws2 As Worksheet
    Set Ws1 = Workbooks("Close_2022").Worksheets("Sheet 1")         ' Assume Data
    Set Ws2 = Workbooks("User_ID").Worksheets("Sheet 2")            ' Assume Master

    Dim LR1, LR2, LR3 As Integer
    LR1 = Ws1.Cells(Ws1.Rows.Count, 3).End(xlUp).Row
    LR2 = Ws2.Cells(Ws2.Rows.Count, 1).End(xlUp).Row
    
    Dim Rng1 As Range
    Dim Rng2 As Range
    Set Rng1 = Ws1.Range("C2:C" & LR1)                                       ' Assume Data
    Set Rng2 = Ws2.Range("A2:A" & LR2)                                       ' Assume Master
    
    Dim x, y As Integer
    Dim Find As Variant
    
    Dim bFound As Boolean

        For y = 1 To LR1
            bFound = False
            For x = 1 To LR2
                If Ws2.Cells(x, 1).Value = Ws1.Cells(y, 3).Value Then
                    bFound = True
                    Exit For
                End If
            Next x
            If bFound = False Then
                Ws1.Cells(y, 3).Interior.Color = vbYellow
            End If
        Next y
End Sub