I need to compare two sheets. I have following code:
Sub RunCompare()
Call compareSheets("Sheet1", "Sheet2")
End Sub
Sub compareSheets(shtSheet1 As String, shtSheet2 As String)
Dim mycell As Range
Dim mydiffs As Integer
'For each cell in sheet2 that is not the same in Sheet1, color it yellow
For Each mycell In ActiveWorkbook.Worksheets(shtSheet1).UsedRange
If Not mycell.Value = ActiveWorkbook.Worksheets(shtSheet2).Cells(mycell.row, mycell.Column).Value Then
mycell.Interior.Color = vbYellow
mydiffs = mydiffs + 1
End If
Next
'Display a message box to demonstrate the differences
MsgBox mydiffs & " differences found", vbInformation
ActiveWorkbook.Sheets(shtSheet1).Select
End Sub
Everything works, but I found one problem: In the sheet1 I do have some rows which are not in sheet2. I need to find those rows and highlight them. That means I have to modify this VBA code following: work with entire row, not with cell and find out, if every row from sheet1 is present in sheet2, if not, highlight entire row if yes, delete this row from sheet1.... but at the same time those rows from sheet1 which are in sheet2 but only one or few value in the row are different, highlight that difference and if the entire row is the same as in sheet2, delete it....that means I will get in sheet1 those rows where are different values, and those rows which are not in sheet2
For example:
Values in Sheet1
A124 |
335 |
403 |
409 |
436 |
579 |
4129 |
A124 |
355 |
403 |
409 |
432 |
350 |
3989 |
A124 |
375 |
401 |
407 |
430 |
80 |
3610 |
A124 |
395 |
401 |
407 |
430 |
0 |
3580 |
A124 |
999 |
401 |
407 |
430 |
0 |
3579 |
Values in Sheet2
A124 |
335 |
403 |
409 |
436 |
579 |
4129 |
A124 |
355 |
403 |
409 |
432 |
350 |
3989 |
A124 |
375 |
301 |
407 |
430 |
80 |
3610 |
Final sheet1
A124 |
375 |
401 |
407 |
430 |
80 |
3610 |
A124 |
395 |
401 |
407 |
430 |
0 |
3580 |
A124 |
999 |
401 |
407 |
430 |
0 |
3579 |
In Final Sheet, we will keep only those rows where are differences, in the first row, there will be highlighted only value 401.00 because in sheet 2 the value is different and the second and third row, there will be highlighted entire row because those rows are not in sheet2.
!!!Note:If I want to identify if the rows from sheet1 is in sheet2, it is enough to work just with combination of first two columns in every rows (those have to be always the same in both sheets, if they are not, the row is missing)....the other columns are for values, which can be different and have to find those differences.
Is it possible to write macro even for this complicated case??? If you could you please help me?
Bookmarks