I want to compare two Excel files and highlight the differences with VBA

0 votes

I need help with this particular code. I compared two Excel workbooks and I want to highlight the differences.. but I'm stuck here in this line which gives me an error:

iRow_M = s1.UsedRange.Rows.Count

here is all code:

Option Explicit
Sub Compare_Two_Excel_Sheets()
    'Define Fields
    Dim Flag As Double
  
    Dim iR As Double, iC As Double, oRw As Double
    Dim iRow_M As Double, iCol_M As Double
    Dim s1 As Workbook, s2 As Workbook
    Dim s3 As Workbook
    
    Flag = 0
    Set s1 = Workbooks.Open(Filename:="C:\new\File1_Path.xlsx")
    Set s2 = Workbooks.Open(Filename:="C:\new\File2_Path.xlsx")
    'Set s3 = Workbook.Sheets(3)
    
    
    iRow_M = s1.UsedRange.Rows.Count
    iCol_M = s1.UsedRange.Columns.Count
    
    For iR = 1 To iRow_M
    For iC = 1 To iCol_M
        s1.Cells(iR, iC).Interior.Color = xlNone
        s2.Cells(iR, iC).Interior.Color = xlNone
        
        If s1.Cells(iR, iC) <> s2.Cells(iR, iC) Then
           s1.Cells(iR, iC).Interior.Color = vbYellow
           s2.Cells(iR, iC).Interior.Color = vbYellow
           
           oRw = oRw + 1
           s3.Cells(oRw, 1) = s1.Cells(iR, iC)
           s3.Cells(oRw, 2) = s2.Cells(iR, iC)
         
          Flag = Flag + 1
         
        End If
        
    Next iC
    Next iR
    
    If Flag > 0 Then
        VBA.Interaction.MsgBox "Differences exist, please check the sheet: DIFF!"
        Else: VBA.Interaction.MsgBox "No differences found!"
    
  End If

End Sub

I want to find differences between these two Excel files: File1_Path.xlsx and File2_Path.xlsx

Jan 13 in Others by Kithuzzz
• 27,740 points
43 views

1 answer to this question.

0 votes

The workbook doesn't have the UsedRange property but WorkSheet has. It should be:

iRow_M = s1.ActiveSheet.UsedRange.Rows.Count
iCol_M = s1.ActiveSheet.UsedRange.Columns.Count

The same goes with Cells as well. Instead of s1 and s2, you should use s1.ActiveSheet and s2.ActiveSheet.

Option Explicit
Sub Compare_Two_Excel_Sheets()
    'Define Fields
    Dim Flag As Double
  
    Dim iR As Double, iC As Double, oRw As Double
    Dim iRow_M As Double, iCol_M As Double
    Dim s1 As Workbook, s2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim s3 As Workbook
    
    Flag = 0
    Set s1 = Workbooks.Open(Filename:="C:\new\File1_Path.xlsx")
    Set s2 = Workbooks.Open(Filename:="C:\new\File2_Path.xlsx")
    'Set s3 = Workbook.Sheets(3)
    Set ws1 = s1.ActiveSheet
    Set ws2 = s2.ActiveSheet
    
    iRow_M = ws1.UsedRange.Rows.Count
    iCol_M = ws1.UsedRange.Columns.Count
    
    For iR = 1 To iRow_M
    For iC = 1 To iCol_M
        ws1.Cells(iR, iC).Interior.Color = xlNone
        ws2.Cells(iR, iC).Interior.Color = xlNone
        
        If ws1.Cells(iR, iC) <> ws2.Cells(iR, iC) Then
           ws1.Cells(iR, iC).Interior.Color = vbYellow
           ws2.Cells(iR, iC).Interior.Color = vbYellow
           
           oRw = oRw + 1
           's3.Cells(oRw, 1) = s1.Cells(iR, iC)
           's3.Cells(oRw, 2) = s2.Cells(iR, iC)
         
          Flag = Flag + 1
         
        End If
        
    Next iC
    Next iR
    
    If Flag > 0 Then
        VBA.Interaction.MsgBox "Differences exist, please check the sheet: DIFF!"
        Else: VBA.Interaction.MsgBox "No differences found!"
    
  End If

End Sub
answered Jan 13 by narikkadan
• 51,240 points

Related Questions In Others

0 votes
1 answer

Want to compare two columns in excel

Hello To compare two columns in excel ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 22,940 points
359 views
0 votes
1 answer

I want to make Excel read a value in Calc and copy it to my sheet in Excel

Here is the sample code that will allow ...READ MORE

answered Oct 27, 2022 in Others by narikkadan
• 51,240 points
74 views
0 votes
1 answer

Can a worksheet ActiveX ComboBox work on a Mac?

ActiveX is an outdated Windows technology that ...READ MORE

answered Nov 17, 2022 in Others by narikkadan
• 51,240 points
66 views
0 votes
0 answers

Strikethrough in Excel VBA

When I enter the second date in ...READ MORE

Nov 27, 2022 in Others by Kithuzzz
• 27,740 points
68 views
0 votes
1 answer

Excel VBA- Creation of a New datablock with criteria

To insert the dropdown, you can go ...READ MORE

answered Jan 12 in Others by narikkadan
• 51,240 points
33 views
0 votes
1 answer

VBA Export as PDF and Save to Location with name as per a Cell in the worksheet

Following is the code that gets generated ...READ MORE

answered Jan 20 in Others by narikkadan
• 51,240 points
40 views
0 votes
1 answer

I have filtered my Excel data and now I want to number the rows. How do I do that?

Solution Filter your data. Select the cells you want ...READ MORE

answered Nov 14, 2022 in Others by narikkadan
• 51,240 points
102 views
0 votes
1 answer

How Can I Round Prices to the nearest 0.95 with an Excel Formula?

Try this: =IF(OR(A3-FLOOR(A3,1)>0.95,A3=CEILING(A3,1)),CEILING ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 51,240 points
112 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP