Comparing multi rows and columns over 2 sheets help please

[JonWhite123]

Hi all,

I have a workbook with two sheets and I want to compare between the two where a system name matches. Both sheets look something like this:

Column C -------Column L
System Fred --- Good
System Trev --- Fair
System Trev --- Poor
System Bob ---- Excellent
System Bob ---- Good
System Nige --- Good

I want to be able to adapt my code so that it can check each line on sheet 1 where the system matches on sheet 2 (e.g. System Trev) and check column L to see if the statuses are the same or have changed. So imagine on sheet 2 System Trev has 2 rows as above but is Fair and Good. I want the code to spot this change and write out something like "Status has changed from Fair and Good to Fair and Poor"

I've been round and round and can't find the answer so hope someone clever can help! The complication is a system may only have one row and one status in column L, but could have 2, 3, 4 or 5.

Below is an example of the code I've written that checks if the system is new on sheet 1. I can't seem to adapt this to work for multiple rows:

' Check for New Systems
With ws1
For r = 2 To ws1end
SysFound = 0
For y = 2 To ws2end
If Cells(r, Columns(System_Name).Column).Value = ws2.Cells(y, Columns(System_Name).Column).Value Or _
Cells(r, Columns(BIA_Ref).Column).Value = ws2.Cells(y, Columns(BIA_Ref).Column).Value Then
SysFound = 1
End If
Next y
If SysFound = 0 Then
Cells(r, Columns("Q").Column).Value = "New System"
End If
Next r
End With

If anyone can help I would be forever in your debt!