Hi mikerickson,
Kind of got this working, at first when changing the code like you suggested, I got a 1004 error, which I've managed to work around changing the code to the following, but it now actually copies 'NewSheet' entirely, and I can't seem to be able to change it to something that only copy/pasts & Highlight just the changes?
Any ideas?
Thanks a lot again!
Sub compareTwoSheets()
Dim sheetOne As Worksheet, sheetTwo As Worksheet
Dim oneRange As Range, twoRange As Range
Dim highlightColor As Long
Dim i As Long, j As Long
Dim OldReport As String
Dim NewReport As String
OldReport = Sheets("Import").Range("K10").Value
NewReport = Sheets("Import").Range("K11").Value
Set sheetOne = ThisWorkbook.Sheets(OldReport)
Set sheetTwo = ThisWorkbook.Sheets(NewReport)
highlightColor = 6: Rem yellow
With sheetOne
Set oneRange = Range(.Range("a1"), .UsedRange)
End With
With sheetTwo
Set twoRange = Range(.Range("a1"), .UsedRange)
End With
With oneRange
Set oneRange = .Resize(Application.Max(.Rows.Count, twoRange.Rows.Count), _
Application.Max(.Columns.Count, twoRange.Columns.Count))
End With
Set twoRange = twoRange.Resize(oneRange.Rows.Count, oneRange.Columns.Count)
Application.ScreenUpdating = False
With twoRange
For i = 1 To .Rows.Count
For j = 1 To .Columns.Count
.Cells(i, j).Copy
Sheets("Sheet1").Paste
Sheets("Sheet1").Cells(i, j).Interior.ColorIndex = highlightColor + (CStr(.Cells(i, j)) = CStr(sheetTwo.Cells(i, j))) * (highlightColor - xlNone)
Sheets("Sheet1").Cells(i, j).Value = .Cells(i, j).Value Next j
Next i
End With
Application.ScreenUpdating = True
End Sub
Bookmarks