Comparing two excel sheets and returns the values that are different in another sheet-CodePudding

I am working on a Project which requires me to compare two excel sheets and need to highlight the differences and also return the unique values.I was able to get it to be work till here but i would need help in copying these returned values in a different sheet but under the same headers meaning the values returned must have their corresponding headers under them. I have written the code till here need further assistance.

Option Explicit

Sub Compare_Two_Excel_Sheets_Highlight_Differences()
    'Define Fields
    Dim iRow As Double, iCol As Double, oRow As Double
    Dim iRow_Max As Double, iCol_Max As Double
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim shOut As Worksheet
    
    'Sheets to be compared
    Set sh1 = ThisWorkbook.Sheets(1)
    Set sh2 = ThisWorkbook.Sheets(2)
    Set shOut = ThisWorkbook.Sheets(3)
    
    'Max Rows
    iRow_Max = sh1.UsedRange.Rows.Count
    iCol_Max = sh1.UsedRange.Columns.Count
    
    'Read Data From Each Sheets of Both Excel Files & Compare Data
    For iRow = 1 To iRow_Max
    For iCol = 1 To iCol_Max
        sh1.Cells(iRow, iCol).Interior.Color = xlNone
        sh2.Cells(iRow, iCol).Interior.Color = xlNone
        
        'Compare Data From Excel Sheets & Highlight the Mismatches
        If sh1.Cells(iRow, iCol) <> sh2.Cells(iRow, iCol) Then
           sh1.Cells(iRow, iCol).Interior.Color = vbYellow
           sh2.Cells(iRow, iCol).Interior.Color = vbYellow
           
           'Write Differences to Output sheet
           oRow = oRow   1
           shOut.Cells(oRow, 1) = sh1.Cells(iRow, iCol)
           shOut.Cells(oRow, 2) = sh2.Cells(iRow, iCol)
        End If
    Next iCol
    Next iRow
    
    'Process Completed
    MsgBox "Task Completed"
    
End Sub

CodePudding user response：

I don't think you need VBA to reach your final goal, Excel has different tools that help sort and organize data (Conditional Formatting, Vlookup function, IF function, etc.) I'm not sure I fully understand what you are trying to do so if this doesn't do it then let me know what you are going for. Here are some articles about Conditional formatting and vlookup: https://www.howtogeek.com/740183/how-to-use-conditional-formatting-to-find-duplicate-data-in-excel/

https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1

CodePudding user response：

I am using VBA because i will have to run it multiple times..!! And every time I will not be able to use V-lookup as it would make things tedious