CODEWITHSUNDEEP
vbaexcel
Jack
Jack

Reputation: 275

Compare Multiple Columns VBA EXCEL (Nested Loops)

I have the following code which compares two different sheets at the same time. 

Dim compareRange As Range
Dim toCompare As Range
Dim rFound As Range
Dim cel As Range

Set compareRange = Worksheets("sheet2").Range("A1:A" & Lastrow3)
Set toCompare = Worksheets("sheet3").Range("A1:A" & Lastrow4)
Set rFound = Nothing

For Each cel In toCompare
    Set rFound = compareRange.Find(cel)
    Z = compareRange.Find(cel).Row
    If Not rFound Is Nothing Then
        cel.EntireRow.Interior.Color = vbGreen
        Set rFound = Nothing
    End If
Next cel

However this only compares column A's when I want to compare column A's C'd and D's at the same time and only pass when all three match. The sheets have duplicate values that is why I need to compare 3 items at a time but some columns are identical. I have to use a nested loop. Any idea where to start?

I thought I could do something like 

Set compareRange = Worksheets("sheet2").Range("A1:A, C1:C, D1:D" & Lastrow3)

But apparently I can't

Upvotes: 1

Views: 3177

Answers (1)

Mrig
Mrig

Reputation: 11702

Actually you are not comparing other two columns in your code. Try below code.

Sub Demo()
    Dim compareRange As Range, toCompare As Range
    Dim lastRow1 As Long, lastRow2 As Long
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim i As Long, j As Long

    Set ws1 = ThisWorkbook.Worksheets("Sheet2")
    Set ws2 = ThisWorkbook.Worksheets("Sheet3")
    lastRow1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
    lastRow2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row

    Set compareRange = ws1.Range("A1:A" & lastRow1)
    Set toCompare = ws2.Range("A1:A" & lastRow2)

    For i = 1 To lastRow2
        For j = 1 To lastRow1
            If ws2.Cells(i, 1) = ws1.Cells(j, 1) And ws2.Cells(i, 3) = ws1.Cells(j, 3) And ws2.Cells(i, 4) = ws1.Cells(j, 4) Then
                ws2.Cells(i, 1).Interior.Color = vbGreen
                Exit For
            End If
        Next j
    Next i
End Sub

Let me know if anything is not clear.

Upvotes: 1

Related Questions