Compare two worksheet with new and missing rows and highlight cells of differences

[YSoS3rius]

Need to compare last years old worksheet "sheet1" to current worksheet "sheet2"
They have the same 12 columns, first column A is unique Request Job Number
Both worksheets are similar but not the exactly the same size about 200 rows
Completed job requests on old worksheet are removed from current worksheet and new job requests are added
Highlight individual cells of changes/difference (ex. location, scheduled dates and time may change)

I have Excel 2016 and have tried:
Conditional Formatting =A1<>sheet1!A1
This works until a new row shows up or removed, then the everything below gets highlighted since its comparing A1 to A1, C3 to C3 and so forth

Also tried
Concatenate each row to a 13th column with Conditional Formatting
=IFERROR(VLOOKUP($A2,Sheet1!$A$2:$L$200,12,FALSE),"")<>$L2
This works but it highlights the entire row. Since status and schedule dates often changes because job request goes out as far as 2024, most of the worksheets gets highlighted.

Appreciate any help

 

[Fluff]

Hi & welcome to MrExcel.
How about
=A2<>INDEX(Sheet1!A$2:A$35,MATCH($A2,Sheet1!$A$2:$A$35,0))

 

[JLGWhiz]

Your objective is not clearly stated but it appears that what you want to know is the changes to your old tasks which are still active, since the new items would all be different from the old list. If you can use vba then this should work.

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, i As Long, c As Range, fn As Range
Set sh1 = Sheets("Sheet1") 'Edit sheet name
Set sh2 = Sheets("Sheet2") 'Edit sheet name
lr = sh1.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
    For Each c In sh1.Range("A2:A" & lr)
        If c <> "" Then
            Set fn = sh2.Range("A:A").Find(c.Value, , xlValues, xlWhole)
                If Not fn Is Nothing Then
                    For i = 2 To 12
                        If sh1.Cells(c.Row, i) <> sh2.Cells(fn.Row, i) Then
                            sh2.Cells(fn.Row, i).Interior.Color = vbYellow
                        End If
                    Next
                End If
        End If
    Next
End Sub

 

[YSoS3rius]

Objective is to:
1) identify new rows of entries
2) identify changes of existing entries
Not too concern about deleted entries that are no longer in the current sheet2

JLGWhiz, your macro works really well for highlighting changes in existing entries, thank you.
What macro code would I need to highlight new entry rows? It would be identifying the unique Job Request Number on column A that is in sheet2 but not in sheet1 and highlight that row

 

[JLGWhiz]

See if this does both jobs.

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, i As Long, c As Range, fn As Range
Set sh1 = Sheets("Sheet1") 'Edit sheet name
Set sh2 = Sheets("Sheet2") 'Edit sheet name
lr = sh1.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
    For Each c In sh1.Range("A2:A" & lr)
        If c <> "" Then
            Set fn = sh2.Range("A:A").Find(c.Value, , xlValues, xlWhole)
                If Not fn Is Nothing Then
                    For i = 2 To 12
                        If sh1.Cells(c.Row, i) <> sh2.Cells(fn.Row, i) Then
                            sh2.Cells(fn.Row, i).Interior.Color = vbYellow
                        End If
                    Next
                End If
        End If
    Next
    For Each c In sh2.Range("A2", sh2.Cells(Rows.Count, 1).End(xlUp))
        If Application.CountIf(sh1.Range("A:A"), c.Value) = 0 Then
            c.EntireRow.Interior.Color = vbYellow
        End If
    Next
End Sub

 

[YSoS3rius]

You are the man, JLGWhiz! Code works to perfection!

 

[JLGWhiz]

Thanks for the feedback,
Regards, JLG