To compare two excel sheets and colour changed cells

The VB macro (below) I tried to adapt for my use is not working. I generate excel report from an application which is updated by different teams in my organisation; it will be very silly of me to be asking every team what they have updated. I want my macro to:
1) arrange all the columns of the new excel report generated as my old excel sheet i.e mirror my old excel sheet.
2) use 2 specific columns (site A and site B columns) from my old excel report (with several other columns) to compare with the new excel report; if there is any new or updated row/column on the new excel report the macro should colour the cells of any row/column that has changed.

These reports will be different workbooks.

Sub Report()

Dim varSheetA As Range
Dim varSheetB As Range
Dim strRangeToCheck As String
Dim iRow As Long
Dim iCol As Long

strRangeToCheck = "A1:IV65536"
' If you know the data will only be in a smaller range, reduce the size of the ranges above.
Debug.Print Now
varSheetA = Worksheets("Sheet1").Range(strRangeToCheck)
varSheetB = Worksheets("Sheet2").Range(strRangeToCheck) ' or whatever your other sheet is.
Debug.Print Now

For iRow = LBound(varSheetA, 1) To UBound(varSheetA, 1)
For iCol = LBound(varSheetA, 2) To UBound(varSheetA, 2)
If varSheetA(iRow, iCol) = varSheetB(iRow, iCol) Then
' Cells are identical.
' Do nothing.
Else
' Cells are different.
' Code goes here for whatever it is you want to do.
End If
Next iCol
Next iRow

End Sub
I will appreciate your help on this.

To compare two excel sheets and colour changed cells



The VB macro (below) I tried to adapt for my use is not working. I generate excel report from an application which is updated by different teams in my organisation; it will be very silly of me to be asking every team what they have updated. I want my macro to:
1) arrange all the columns of the new excel report generated as my old excel sheet i.e mirror my old excel sheet.
2) use 2 specific columns (site A and site B columns) from my old excel report (with several other columns) to compare with the new excel report; if there is any new or updated row/column on the new excel report the macro should colour the cells of any row/column that has changed.

Updated cells are highlighted in yellow on Workbook named NEW.

These reports are on different workbooks.

Please Login or Register  to view this content.

I will appreciate your help on this.

Thanks but I was getting 'subscript out of range' error when I tried to use it on both excel sheets. What else can I do?

Yes, it was after I changed the book names that I received that error. What else should I try? Thanks. I appreciate your help.

Thanks, guys, it worked. My mistake, I missed out spaces in the names of my sheet. Apologies.