Find rows with a common item and find or highlight difference



Is there a way to find rows that have a common item in say column A and then
find and list or highlight the differences between them (the rows)? There
could be different items on each row but there also can be the same item on
more than one row.

Hope this makes sense.


You could probably do it using the Find method for a single item. I can't
visualize doing it for more than one, since the range of rows could overlap
and distort the results. Start with A2 as the first source cell. Search,
beginning in A3, through
the last row, move down one and continue the process until a match is found
or the range is exhausted.

Sub hilite()
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 3 To lastRow
For Each c In Range("A" & i & ":A" & lastRow)
If c.Value = Range("A" & i - 1).Value Then
Rows(i - 1 & ":" & c.Row).Interior.ColorIndex = 5
Exit Sub
End If
End Sub


You probably noticed that I did not use the find method. But I still
terminated the search after the first match for the same reason stated.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question