Before commenting on saying that there are similar questions, Ive tried them but they do not work unfortunately
Hi, this is the first time I am on S.O, rest assured, I have spent hours looking for a solution for this. I have a status column which shows statuses such as, deleted, new, changed. When the status is "changed", I would like to compare that particular row from column E to the last possible column in Excel (XFD) in Sheet3 to columns A to the last possible column in Excel (XFD) in Sheet1 and highlight the cells which are different.
I have found this solution:-
Dim diffB As Boolean Dim r As Long, c As Integer, m As Integer Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String Dim rptWB As Workbook, DiffCount As Long Application.ScreenUpdating = False Application.StatusBar = "Creating the report..." Application.DisplayAlerts = True With Sheet1.UsedRange lr1 = .Rows.Count lc1 = .Columns.Count End With With Sheet3.UsedRange lr2 = .Rows.Count lc2 = .Columns.Count End With maxR = lr1 maxC = lc1 If maxR < lr2 Then maxR = lr2 If maxC < lc2 Then maxC = lc2 DiffCount = 0 For c = 1 To maxC For i = 2 To lr1 diffB = True Application.StatusBar = "Comparing cells " & Format(i / maxR, "0 %") & "..." For r = 2 To lr2 cf1 = "" cf2 = "" On Error Resume Next cf1 = Sheet1.Cells(i, c).FormulaLocal cf2 = Sheet3.Cells(r, c).FormulaLocal On Error GoTo 0 If cf1 = cf2 Then diffB = False Sheet1.Cells(i, c).Interior.ColorIndex = 19 Sheet1.Cells(i, c).Select Selection.Font.Bold = True Exit For End If Next r If diffB Then DiffCount = DiffCount + 1 Sheet1.Cells(i, c).Interior.ColorIndex = 0 Sheet1.Cells(i, c).Select Selection.Font.Bold = False End If Next i Next c3 Application.StatusBar = "Formatting the report..." 'Columns("A:IV").ColumnWidth = 10 m = maxR - DiffCount - 1 Application.StatusBar = False Application.ScreenUpdating = True MsgBox m & " cells contain same values!", vbInformation, _ "Compare " & Sheet1.Name & " with " & Sheet3.Name
However, this compares columns and I do not know how to limit the comparison to column E-XFD in sheet1 to column A-XFD in sheet2.
There are also several sheets in this workbook but I only want to compare sheet1 and sheet2.
It will be much appreciated if you guys can help me out :)