Compare data between sheets and highlight match

[Trueblue862]

Hi, I need a macro to compare the data on two sheets, sheet1 and sheet2, and then highlight any matches on sheet1. I found this piece but I'm having trouble reworking it to just highlight the matches. I'm getting some unexpected results when I try and adjust it. Any help would be appreciated.

Sub RunCompare()
    Call compareSheets("Sheet1", "Sheet2")
End Sub

Sub compareSheets(shtSheet1 As String, shtSheet2 As String)

    Dim i As Long
    Dim j As Long
    Dim c As Long
    Dim numb2 As Long
    Dim numb As Long
    Dim ColRow As Long

    numb = Sheets(shtSheet1).Cells(Rows.Count, 1).End(xlUp).Row
    numb2 = Sheets(shtSheet2).Cells(Rows.Count, 1).End(xlUp).Row

    ColRow = Sheets(shtSheet2).UsedRange.Columns.Count

    ' remove the next 3 lines if you don't want to clear the backgrounds
    With Sheets(shtSheet2)
    .Range(.Cells(1, 1), .Cells(numb2, ColRow)).Interior.Pattern = xlNone
    End With

    For i = numb To 1 Step -1
        For j = numb2 To 1 Step -1
            If ActiveWorkbook.Sheets(shtSheet1).Range("A" & i) <> "" And ActiveWorkbook.Sheets(shtSheet1).Range("A" & i) = ActiveWorkbook.Sheets(shtSheet2).Range("A" & j) Then
                For c = 1 To ColRow
                    If ActiveWorkbook.Sheets(shtSheet1).Cells(i, c) = ActiveWorkbook.Sheets(shtSheet2).Cells(j, c) Then
                        ActiveWorkbook.Sheets(shtSheet2).Cells(j, c).Interior.Color = vbYellow
                    End If
                Next
            End If
        Next
    Next

End Sub

 

[Saurabhj]

Hi, What issue are you getting ?

I copied the code and it's highlighting the similar data in sheet2. For me it's working fine.

 

[Trueblue862]

The issue I was having was when I converted it to highlight on sheet1 it was highlighting seemingly random columns as well as column A.
The issue was with the “c” variable. I don’t know why but it was returning seemingly random numbers as well as 1. My work around was to wrap up the below section of code in an if statement, like so.
Sorry about the code looking messy, I’m replying from my phone.

If c=1 then
If ActiveWorkbook.Sheets(shtSheet2).Cells(i, c) = ActiveWorkbook.Sheets(shtSheet1).Cells(j, c) Then
                        ActiveWorkbook.Sheets(shtSheet1).Cells(j, c).Interior.Color = vbYellow
                    End If
End If

 

[Saurabhj]

Hi,

If I use the condition as below, it will highlight only first column.

    For i = numb To 1 Step -1
        For j = numb2 To 1 Step -1
            If ActiveWorkbook.Sheets(shtSheet1).Range("A" & i) <> "" And ActiveWorkbook.Sheets(shtSheet1).Range("A" & i) = ActiveWorkbook.Sheets(shtSheet2).Range("A" & j) Then
                For c = 1 To ColRow
                    If c = 1 Then
                        If ActiveWorkbook.Sheets(shtSheet1).Cells(i, c) = ActiveWorkbook.Sheets(shtSheet2).Cells(j, c) Then
                            ActiveWorkbook.Sheets(shtSheet1).Cells(j, c).Interior.Color = vbYellow
                        End If
                    End If
                Next
            End If
        Next
    Next

 

[Trueblue862]

Yes, sorry, in my last post I meant that I had managed to get a work around, but I don’t know the root cause of the issue. At the end of the day it probably doesn’t matter.

 

[diddi]

try replacing your loop with this

    For i = numb To 1 Step -1
        For j = numb2 To 1 Step -1
            If ActiveWorkbook.Sheets(shtSheet1).Range("A" & i) <> "" And ActiveWorkbook.Sheets(shtSheet1).Range("A" & i) = ActiveWorkbook.Sheets(shtSheet2).Range("A" & j) Then
                If ActiveWorkbook.Sheets(shtSheet1).Cells(i, 1) = ActiveWorkbook.Sheets(shtSheet2).Cells(j, 1) Then
                    ActiveWorkbook.Sheets(shtSheet2).Cells(j, 1).Interior.Color = vbYellow
                End If
            End If
        Next
    Next

or this

    With Sheets(shtSheet1)
        For i = numb To 1 Step -1
            For j = numb2 To 1 Step -1
                If .Range("A" & i) <> "" And .Range("A" & i) = Sheets(shtSheet2).Range("A" & j) Then
                    If .Cells(i, 1) = Sheets(shtSheet2).Cells(j, 1) Then
                        Sheets(shtSheet2).Cells(j, 1).Interior.Color = vbYellow
                    End If
                End If
            Next
        Next
    End With

 

[Trueblue862]

Thank you diddi, I’ll give it a try tomorrow.