Macro to compare sheet1 and sheet2 data in given range

[Prasad K]

i have applied this vba macro on my excel sheet to compare sheet1 and sheet2 data in given range & if any different data should found in sheet1 or sheet2 in given range then highlight that row data with color
and if i remove or delete that highlighted row data in that sheet then the highlighted color should be remove

can any one correct this code

Sub CompareHighlight()
Dim varSheetA As Variant
Dim varSheetB As Variant
Dim strRangeToCheck As String
Dim iRow As Long
Dim iCol As Long
Set varSheetA = Sheet1
Set varSheetB = Sheet2
strRangeToCheck = "A1:B11"

Debug.Print Now
varSheetA = Worksheets("Sheet1").Range(strRangeToCheck)
varSheetB = Worksheets("Sheet2").Range(strRangeToCheck)
Debug.Print Now

For iRow = LBound(varSheetA, 1) To UBound(varSheetA, 1)
    For iCol = LBound(varSheetA, 2) To UBound(varSheetA, 2)
        If varSheetA(iRow, iCol) = varSheetB(iRow, iCol) Then
         varSheetA(iRow, iCol).Interior.ColorIndex = 27
         varSheetB(iRow, iCol).Interior.ColorIndex = 27
         Else
         varSheetA(iRow, iCol).Interior.ColorIndex = xlNone
         varSheetB(iRow, iCol).Interior.ColorIndex = xlNone
                     
        Else
        End If
        End If
    Next iCol
Next iRow
End Sub

 

[Alex Blakenburg]

Ideally I need to know a bit more about what you want to get out of this because the code is not terribly practical and if you are using it to practice I need to know what you want to learn.

The code is trying to compare the exact same cell on one sheet with the same cell on another. sheet. In that context the below statement doesn't make sense because it would throw everything that did match below that point out of alignment.

if i remove or delete that highlighted row data in that sheet then the highlighted color should be remove

The code tries to use varSheetA & B for 2 different purposes.
While using an array will be faster you can't apply an Interior.Color to an array.

I have tried to stick to your overall program flow.

Sub CompareHighlight()
Dim varSheetA As Worksheet      'XXX change to worksheet - code used Set varSheetA = Sheet1
Dim varSheetB As Worksheet      'XXX change to worksheet - code used Set varSheetB = Sheet2
Dim strRangeToCheck As String
Dim iRow As Long
Dim iCol As Long

Dim varRngA As Range            'XXX code was re-using varSheetA as a variant array
Dim varRngB As Range            'XXX code was re-using varSheetB as a variant array

Set varSheetA = Sheet1          ' XXX This is the same thing as Worksheets("Sheet1")
Set varSheetB = Sheet2          ' XXX This is the same thing as Worksheets("Sheet2")
strRangeToCheck = "A1:B11"

' XXX Code was trying to apply Interior color to an element in an array not to a Cell.
' XXX changed from using array element varSheetAorB(iRow, iCol) to cell varRngAorB.Cells(iRow, iCol)
Set varRngA = varSheetA.Range(strRangeToCheck)
Set varRngB = varSheetB.Range(strRangeToCheck)

For iRow = 1 To varRngA.Rows.Count
    For iCol = 1 To varRngA.Columns.Count
        If varRngA.Cells(iRow, iCol).Value2 <> varRngB.Cells(iRow, iCol).Value2 Then
            varRngA.Cells(iRow, iCol).Interior.ColorIndex = 27
            varRngB.Cells(iRow, iCol).Interior.ColorIndex = 27
         Else
            varRngA.Cells(iRow, iCol).Interior.ColorIndex = xlNone
            varRngB.Cells(iRow, iCol).Interior.ColorIndex = xlNone
        End If
    Next iCol
Next iRow
End Sub

 

[Prasad K]

Ideally I need to know a bit more about what you want to get out of this because the code is not terribly practical and if you are using it to practice I need to know what you want to learn.

The code is trying to compare the exact same cell on one sheet with the same cell on another. sheet. In that context the below statement doesn't make sense because it would throw everything that did match below that point out of alignment.


The code tries to use varSheetA & B for 2 different purposes.
While using an array will be faster you can't apply an Interior.Color to an array.

I have tried to stick to your overall program flow.

Sub CompareHighlight()
Dim varSheetA As Worksheet      'XXX change to worksheet - code used Set varSheetA = Sheet1
Dim varSheetB As Worksheet      'XXX change to worksheet - code used Set varSheetB = Sheet2
Dim strRangeToCheck As String
Dim iRow As Long
Dim iCol As Long

Dim varRngA As Range            'XXX code was re-using varSheetA as a variant array
Dim varRngB As Range            'XXX code was re-using varSheetB as a variant array

Set varSheetA = Sheet1          ' XXX This is the same thing as Worksheets("Sheet1")
Set varSheetB = Sheet2          ' XXX This is the same thing as Worksheets("Sheet2")
strRangeToCheck = "A1:B11"

' XXX Code was trying to apply Interior color to an element in an array not to a Cell.
' XXX changed from using array element varSheetAorB(iRow, iCol) to cell varRngAorB.Cells(iRow, iCol)
Set varRngA = varSheetA.Range(strRangeToCheck)
Set varRngB = varSheetB.Range(strRangeToCheck)

For iRow = 1 To varRngA.Rows.Count
    For iCol = 1 To varRngA.Columns.Count
        If varRngA.Cells(iRow, iCol).Value2 <> varRngB.Cells(iRow, iCol).Value2 Then
            varRngA.Cells(iRow, iCol).Interior.ColorIndex = 27
            varRngB.Cells(iRow, iCol).Interior.ColorIndex = 27
         Else
            varRngA.Cells(iRow, iCol).Interior.ColorIndex = xlNone
            varRngB.Cells(iRow, iCol).Interior.ColorIndex = xlNone
        End If
    Next iCol
Next iRow
End Sub

Hi Alex

This your code is working perfectly

here if different data have found in sheet1 or sheet2 then highlight that data with color in that sheet only here what happening if different data should found in any one sheet this code is highlighting in both sheets

here how i want to compare and highlight & this code will compare sheet1 data and sheet2 data in given range if both sheets have same data then do nothing if any sheet have found different data then that data row should be highlight on that sheet only not on both sheets

 

[Alex Blakenburg]

You need to provide sample data showing sheet1 and sheet2 and what results you are expecting.
If sheet1 and sheet2 are different, then how is the code supposed to tell which one is different.
The only thing you could do it to highlight a row if it is on one sheet and not on the other. This is quite different to what your original code is doing.

Depending on what you are trying to do Power Query might be a better option.

 

[Prasad K]

You need to provide sample data showing sheet1 and sheet2 and what results you are expecting.
If sheet1 and sheet2 are different, then how is the code supposed to tell which one is different.
The only thing you could do it to highlight a row if it is on one sheet and not on the other. This is quite different to what your original code is doing.

Depending on what you are trying to do Power Query might be a better option.

SPLIT.xlsm
	A	B
1	Arya	E-1101
2	Bhaskar	E-1102
3	Ganesh	E-1103
4	Pavan	E-1104
5	Pradeep	E-1105
6	Arun	E-1106
7	Banu	E-1107
8	Guru	E-1108
9	Prakash	E-1109
10	Gaurav	E-1110
11	Mani	F-1010
Sheet1

SPLIT.xlsm
	A	B
1	Arya	E-1101
2	Bhaskar	E-1102
3	Ganesh	E-1103
4	Pavan	E-1104
5	Pradeep	E-1105
6	Arun	E-1106
7	Banu	E-1107
8	Guru	E-1108
9	Prakash	E-1109
10	Gaurav	E-1110
11
Sheet2

this is sample data

Mani F-1010 is the different data

 

[Alex Blakenburg]

So you want to highlight anything that is one one sheet and not on the other, is that right ?

How much data are you talking about having ?

What are you going to do with the highlighted ones ?
Power query would be able to give you a list of what is missing.

 

[Prasad K]

So you want to highlight anything that is one one sheet and not on the other, is that right ?

How much data are you talking about having ?

What are you going to do with the highlighted ones ?
Power query would be able to give you a list of what is missing.

Power query

What are you going to do with the highlighted ones ?

i will delete highlighted data on that row

and i don't know how to use power query in excel

once again thank you for given a code

 

[Alex Blakenburg]

I can't see the point in reinventing the wheel, so if I understand your updated requirements correctly this modified version of @Eric W's code should work for you.
Ref: 'https://www.mrexcel.com/board/threads/highlight-row-if-it-does-not-match-any-rows-on-another-sheet.1177902/

Run CompareLists and it will call Eric's procedure (the 2nd one below)

Sub CompareLists()
    Dim rng1 As Range, rng2 As Range, rngSwitch
    Dim strRangeToCheck As String
    
    strRangeToCheck = "A1:B11"
    ' Set this range to the table that you want hilighted
    Set rng1 = Sheets("Sheet1").Range(strRangeToCheck)
    ' Set this range to the table you're comparing with
    Set rng2 = Sheets("Sheet2").Range(strRangeToCheck)
    
    Call HiLiteRows(rng1, rng2)
    
    ' Reverse the lookup
    Set rngSwitch = rng1
    Set rng1 = rng2
    Set rng2 = rngSwitch
    
    Call HiLiteRows(rng1, rng2)

End Sub

'https://www.mrexcel.com/board/threads/highlight-row-if-it-does-not-match-any-rows-on-another-sheet.1177902/
'Eric W
Private Sub HiLiteRows(rng1 As Range, rng2 As Range)

Dim cmp1 As Variant
Dim i As Long, j As Long, k As Long, op As Range
    
' Set this array to the columns you want to compare.  So in this example, we're comparing
' column 1 of table1 with column 1 of table2, then column 3 of table1 with column 2 of table2
' Add as many as you want
    cmp1 = Array(1, 1, 2, 2)
    
' Clear formatting
    rng1.Interior.Color = xlNone
' Set the output range to nothing
    Set op = Nothing
    
    Dim d1, d2
    
    d1 = rng1.Value
    d2 = rng2.Value
    For i = 1 To UBound(d1)
        For j = 1 To UBound(d2)
            For k = 0 To UBound(cmp1) - 1 Step 2
                If d1(i, cmp1(k)) <> d2(j, cmp1(k + 1)) Then Exit For
            Next k
            If k > UBound(cmp1) Then Exit For
        Next j
        If j > UBound(d2) Then
            If op Is Nothing Then
                Set op = rng1.Offset(i - 1).Resize(1)
            Else
                Set op = Union(op, rng1.Offset(i - 1).Resize(1))
            End If
        End If
    Next i
                
    If op Is Nothing Then Exit Sub
    op.Interior.ColorIndex = 27
    
End Sub

 

[Marc L]

According to post #5 attachment it should be as simple as this VBA demonstration :​

Sub Demo1()
        Dim Rg As Range, R&
        Set Rg = [Sheet1!A1].CurrentRegion.Rows
        Application.ScreenUpdating = False
    With [Sheet2!A1].CurrentRegion.Rows
        For R = 1 To Rg.Count
            If IsError(Application.Match(Rg.Cells(R, 2), .Columns(2), 0)) Then Rg(R).Interior.Color = vbYellow
        Next
        For R = 1 To .Count
            If IsError(Application.Match(.Cells(R, 2), Rg.Columns(2), 0)) Then .Item(R).Interior.Color = vbYellow
        Next
    End With
        Application.ScreenUpdating = True
        Set Rg = Nothing
End Sub