I am trying to compare two sheets with the same information on both. On sheet1 = my inventory on sheet two is my data that I want to compare with sheet1 and have the differences on sheet3.
I have tried this Macro....
Sub COMPARESHEET()
Dim erowsht1, erowsht2, erow As Long
Dim ecolsht1, ecolsht2, ecol As Long
Dim row, col As Integer
row = 2
col = 1
'find the lastrow on both sheets
erowsht1 = Sheet1.Cells(1, 1).CurrentRegion.Rows.Count
erowsht2 = Sheet2.Cells(1, 1).CurrentRegion.Rows.Count
'which is the greater of the two that will be the end row
If erowsht2 >= erowsht1 Then
erow = erowsht2
Else
erow = erowsht1
End If
'find the last column in both sheets
'which ever is the larges colunn number will be the end column
ecolsht1 = Sheet1.Cells(1, 1).CurrentRegion.Columns.Count
ecolsht2 = Sheet2.Cells(1, 1).CurrentRegion.Columns.Count
If ecolsht2 >= ecolsht1 Then
ecol = erowsht2
Else
ecol = ecolsht1
End If
'start with a1 and go to last row and last column in both sheets
Do Until row = erow + 1
For col = 1 To ecol + 1
If Worksheets("Sheet1").Cells(row, col) <> Worksheets("Sheet2").Cells(row, col) Then
Worksheets("Sheet2").Cells(row, col).Interior.ColorIndex = 5
'Worksheets("Sheet3").Cells(row, col).Value = Worksheets("Sheet2").Cells(row, col)
End If
Next col
row = row + 1
Loop
End Sub