Macro to compare sheet1 and sheet2 data in given range

Prasad K

Board Regular
Joined
Aug 4, 2021
Messages
160
Office Version
  1. 2016
  2. 2007
Platform
  1. Windows
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

VBA 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
 

Attachments

  • s1.PNG
    s1.PNG
    25.8 KB · Views: 21

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
3,530
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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

Board Regular
Joined
Aug 4, 2021
Messages
160
Office Version
  1. 2016
  2. 2007
Platform
  1. Windows
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.

VBA Code:
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

MrExcel MVP
Joined
Feb 23, 2021
Messages
3,530
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Aug 4, 2021
Messages
160
Office Version
  1. 2016
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

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
AB
1AryaE-1101
2BhaskarE-1102
3GaneshE-1103
4PavanE-1104
5PradeepE-1105
6ArunE-1106
7BanuE-1107
8GuruE-1108
9PrakashE-1109
10GauravE-1110
11ManiF-1010
Sheet1



SPLIT.xlsm
AB
1AryaE-1101
2BhaskarE-1102
3GaneshE-1103
4PavanE-1104
5PradeepE-1105
6ArunE-1106
7BanuE-1107
8GuruE-1108
9PrakashE-1109
10GauravE-1110
11
Sheet2



this is sample data

Mani F-1010 is the different data
 

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
3,530
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Aug 4, 2021
Messages
160
Office Version
  1. 2016
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 23, 2021
Messages
3,530
Office Version
  1. 365
Platform
  1. Windows
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)

VBA Code:
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

Banned User
Joined
Apr 5, 2021
Messages
2,030
Office Version
  1. 2010
Platform
  1. Windows
According to post #5 attachment it should be as simple as this VBA demonstration :​
VBA Code:
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
 
Solution
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,440
Messages
5,831,643
Members
430,079
Latest member
lucasabreueng

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top