Compare each complete row of sheet2 with sheet3 each complet

[leonardo1234]

We have to compare each complete row of sheet2 with sheet3 each complete row and if sheet2 complete row match with sheet3 complete row then highlight it with green colour in sheet4 in coloumn B

[HansV]

Welcome to Eileen's Lounge!

I am away from home at the moment; if nobody else replies, I will look at your question later today.

[leonardo1234]

Thnx HansV for ur great support and giving ur precious time to my post
Take ur time HansV i will wait

[HansV]

Here is a macro you can use:

Code: Select all

Sub CompareSheets()
    Dim ws2 As Worksheet
    Dim ws3 As Worksheet
    Dim ws4 As Worksheet
    Dim r2 As Long
    Dim m2 As Long
    Dim n2 As Long
    Dim r3 As Long
    Dim m3 As Long
    Dim n3 As Long
    Dim c As Long
    Dim f As Boolean
    Application.ScreenUpdating = False
    Set ws2 = Worksheets("Sheet2")
    m2 = ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Row
    Set ws3 = Worksheets("Sheet3")
    m3 = ws3.Cells(ws3.Rows.Count, 1).End(xlUp).Row
    Set ws4 = Worksheets("Sheet4")
    ' Loop through the rows of Sheet2
    For r2 = 1 To m2
        n2 = ws2.Cells(r2, ws2.Columns.Count).End(xlToLeft).Column
        ' Loop through the rows of Sheet3
        For r3 = 1 To m3
            n3 = ws3.Cells(r3, ws3.Columns.Count).End(xlToLeft).Column
            If n3 = n2 Then
                f = True
                For c = 1 To n2
                    If ws2.Cells(r2, c).Value <> ws3.Cells(r3, c).Value Then
                        f = False
                        Exit For
                    End If
                Next c
                If f Then
                    ws4.Cells(r2, 2).Interior.Color = RGB(0, 128, 0)
                End If
            End If
        Next r3
    Next r2
    Application.ScreenUpdating = True
End Sub

[leonardo1234]

Thnx HansV for givng ur precious time and great support to this post
Problem solved

[pecoflyer]

This question was cross posted at :
excelforum
chandoo's
excelguru.ca
( and probably others)

[HansV]

@leonardo1234: we don't object to posting the same question in multiple forums. But if you do so, you should always mention this explicitly, and include links to your question on the other forums. That way, members can check whether the problem has already been solved, and avoid wasting their time if so.

[leonardo1234]

I have mentioned the link of this website to all my posted question

[leonardo1234]

i am new thats y i made this mistake but from yesterday night after knowing the term cross posting i am not doing the same mistake from yesterday night Sorry Hansv Sir

[HansV]

OK, thank you very much.

[leonardo1234]

Thank u HansV for giving ur precious time and great support to my vba problem

[leonardo1234]

HansV Sir this code is working slow
i have 1.5lakh rows and rows are big they are till xfd
so it is my request plz look into it and if possible then make this code fast

[HansV]

With that many rows and columns, Excel has to perform a huge number of comparisons, I'm not sure it'll be possible to make the code much faster. But I'll have a look later today.

[leonardo1234]

No problem HansV Sir, take ur time

[HansV]

If each row on sheet2 can have only one match on sheet3, you can speed up the code as follows:

Code: Select all

Sub CompareSheets()
    Dim ws2 As Worksheet
    Dim ws3 As Worksheet
    Dim ws4 As Worksheet
    Dim r2 As Long
    Dim m2 As Long
    Dim n2 As Long
    Dim r3 As Long
    Dim m3 As Long
    Dim n3 As Long
    Dim c As Long
    Dim f As Boolean
    Application.ScreenUpdating = False
    Set ws2 = Worksheets("Sheet2")
    m2 = ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Row
    Set ws3 = Worksheets("Sheet3")
    m3 = ws3.Cells(ws3.Rows.Count, 1).End(xlUp).Row
    Set ws4 = Worksheets("Sheet4")
    ' Loop through the rows of Sheet2
    For r2 = 1 To m2
        n2 = ws2.Cells(r2, ws2.Columns.Count).End(xlToLeft).Column
        ' Loop through the rows of Sheet3
        For r3 = 1 To m3
            n3 = ws3.Cells(r3, ws3.Columns.Count).End(xlToLeft).Column
            If n3 = n2 Then
                f = True
                ' Loop through the columns
                For c = 1 To n2
                    If ws2.Cells(r2, c).Value <> ws3.Cells(r3, c).Value Then
                        f = False
                        Exit For
                    End If
                Next c
                ' Do we have a match?
                If f Then
                    ws4.Cells(r2, 2).Interior.Color = RGB(0, 128, 0)
                    Exit For
                End If
            End If
        Next r3
    Next r2
    Application.ScreenUpdating = True
End Sub

[leonardo1234]

Thnx Hansv for giving ur precious time and great support to this post
This code is faster than previous one Thnx alot

[Doc.AElstein]

I have mentioned the link of this website to all my posted question

Not quite ....
Cross Post:
http://www.excelfox.com/forum/showthrea ... mplete-row" onclick="window.open(this.href);return false;

Alan