Hi guys,
I hope youre all doing well! So I want to compare two sheets and check if the entries of sheet 1 exist in sheet 2 and vice versa. The first challenge is that one "entry" is identified by all columns of one row. The second challenge is that there is no order of the rows.
So i started a VBA macro which highlights the cells in sheet 2 which are not found in sheet 1.
The code works fine when I have a primary key in the first column. But when I have different entries which only match in the first column, my code is not working properly. I somehow need to check the combination of all columns. So I need to concat the value of each column of a row and compare them (sheet 1 and sheet 2) or something similiar.
Im really new to VBA so please go easy on me! Here is my code:
I really dont need the highlighting, I just want to see which row is different in sheet 2 by e.g. new column called Expected Results. Heres an example file: example.xlsmSub CompareSheets(shtSheet1 As String, shtSheet2 As String) Dim c As Integer, j As Integer, i As Integer, mydiffs As Integer, cnt1 As Integer, cnt2 As Integer Dim noexist As Integer 'count the rows of each sheet cnt2 = Worksheets("Sheet2").Cells.SpecialCells(xlCellTypeLastCell).Row cnt1 = Worksheets("Sheet1").Cells.SpecialCells(xlCellTypeLastCell).Row 'For each cell in sheet2 that is not the same in Sheet1, color it yellow For i = 1 To cnt2 For j = 1 To cnt1 If ActiveWorkbook.Worksheets(shtSheet2).Cells(i, 1).Value = ActiveWorkbook.Worksheets(shtSheet1).Cells(j, 1).Value Then For c = 2 To 22 If Not ActiveWorkbook.Worksheets(shtSheet2).Cells(i, c).Value = ActiveWorkbook.Worksheets(shtSheet1).Cells(j, c).Value Then ActiveWorkbook.Worksheets(shtSheet2).Cells(i, c).Interior.Color = vbYellow mydiffs = mydiffs + 1 End If Next Exit For End If 'when sheet2 has a new entry (meaning value is not in sheet1), color it red If j = cnt1 Then ActiveWorkbook.Worksheets(shtSheet2).Cells(i, 1).Interior.Color = vbRed noexist = noexist + 1 End If Next Next ActiveWorkbook.Sheets(shtSheet2).Select End Sub
When you execute my Code, youll see I get a much different result than I need.
If I didnt explain my problem well enough, please feel free to ask. I would appreciate any help! Thank you in advance.
Best regards
Alex