VBA Code compare two spread sheets and highlight difference errors out on no match

I need to compare two spread sheets that have the same employee data structure, but the data in each cell might be in a different and the list will not be in the same order each week with new hires and separations. Each week I upload data in a file and I want to compare last weeks data with the new weeks data and highlight the differences on the new report. The code I have below does this exactly how I want it to, but there is one issue. It errors out when it comes to a new employee that wasn't on the previous weeks report. I understand why it errors out, because the match function doesn't find a match. But I am not sure how I can get around this error? I have tried adding a error handler, but I couldn't get it to work right. I tired using on error resume next, but that creates another issue.

Below is the code I created. My end game would be to highlight the cells that had data changes in yellow (which it current does) and highlight new employees row in red. If possible, I would also like to be able to identify from the old report who is not on the new report and copy that row of data to the new report and highlight a difference color to easily identify who was removed.

Thank You for taking the time to help.

Sub Compare()

Dim LR As Integer
Dim I As Integer
Dim R As Long
Dim C As Integer

'On Error GoTo ErrorHandler

LR = Worksheets("New").Range("A20000").End(xlUp).Row

For I = 1 To LR

           R = Application.WorksheetFunction.Match(Worksheets("New").Cells(I, 1).Value, Worksheets("Previous").Range("A:A"), 0)
    
           For C = 1 To 5
    
               If Not Worksheets("New").Cells(I, C).Value = Worksheets("Previous").Cells(R, C).Value Then
        
               Worksheets("New").Cells(I, C).Interior.Color = vbYellow
        
               End If
    
           Next C
1:
  Next I
   
    
  'ErrorHandler:

'Worksheets("New").Cells(I, 1).Interior.Color = vbRed
 
'GoTo 1:

End Sub

Adding the on error resume next to that location does not fail, but it does not do what I want. When it resumes next, it does not update R to the new row number which then causes it to highlight cells that did not change. Below screenshot shows what I am talking about. In column A, number 8 (row 3), number 9 (row 4) and number 7 (row7) were not on the previous data. You can see it highlighted the data in column C on these rows. It did this because when it error out, it continues to compare the last row it was on.

example.PNG

Holger,

I get a compile error. Sub or Function not defined and it highlights the IsErr.

Hi Sourabh,

I was watching threads in this forum.. This is the great help before I posted thread, I was checking that is there any examples available in this forum.

Now I got it . I need help, In the attached sheet , instead of using colors, can u pls help to create new sheet only for changes and new added.

Thanks
Kumar PK