Thread: Highlighting differences between two sets of data

Hi,

I produce two lists at various times in a month (List A & List B) Id like to compare List B against List A and show:

1) New names on List B
2) Any students who have had their Student Status change on list B.

I have used conditional formatting to do part 1, but Im stuck on part 2. So for example in the attached example I would like:

The cells I6:N6 in List B to be highlighted red to show that Mary Green has changed from 'On Course' in List A to 'Left' in list B. Both lists would be no more than 565 rows (B3:G565) and (I3:N565)

Could someone help me please. Ive found variations on here and elsewhere but not quite exactly what I need it to do…Any help would be just

(edit - apologies, i guess this could/should have gone in the standard forum as it might not need a macro to do this)

Hi.

Take a look to the attached file.

Is this what are you looking for?

Slightly adapted from here, try this

Sub kyle()

Dim str As String
Dim cRow As Integer


With Sheets("Find Duplicates")
    For cRow = 3 To .Range("B" & .Rows.Count).End(xlUp).Row
        str = str & Join(Application.Transpose(Application.Transpose(.Cells(cRow, 2).Resize(1, 6))), "|") & "~"
    Next
    For cRow = 3 To .Range("I" & .Rows.Count).End(xlUp).Row
        If InStr(1, str, Join(Application.Transpose(Application.Transpose(.Cells(cRow, 9).Resize(1, 6))), "|") & "~", vbTextCompare) = 0 Then
            .Cells(cRow, 9).Resize(1, 6).Interior.Color = vbRed
        End If
    Next
End With

End Sub

Ηι

You only need to erase the absolute values ​​in cells($)

See the attached file

In my code? I'm pretty sure it is?

Really don't understand what you mean. Can you post me an example?

Please forgive me, but "computer time" is over for me for Today.

I'll see it tomorrow morning.

Originally Posted by Kyle123

In my code? I'm pretty sure it is?

In list B Student ID 2 is Joanne Smith. replace this entry with a new name say Peter Smith (which is not available in list A) and add Joanne Smith entry at the bottom of list with no change in the student status. Now we have 6 entries in List B where as 5 in List A. Now run your macro. You'll find that Joanne Smith is also highlighted in Red even though the entry in List B is exactly same as List A.This is happening because of the change of order of the names.

My code works fine on my computer?