CAE5942
asked on
VBA code that detects duplicates in a column
Hi everyone,
I have a spreadsheet where one of the columns contains a list of names (both first and last names) and I've sorted the column so that it's in alphabetical order. Some of the rows in that column contain duplicate names so I was wondering if I could get some help with some vba code that would look down the column (across all rows) and see if it finds the same name and if it does then the consecutive rows would be highlighted a yellow colour? The same names might be in row 1 to 4 or it could be in rows 7 and 8 or any other consecutive rows.
The names would be in Column G.
Would really appreciate any help.
I have a spreadsheet where one of the columns contains a list of names (both first and last names) and I've sorted the column so that it's in alphabetical order. Some of the rows in that column contain duplicate names so I was wondering if I could get some help with some vba code that would look down the column (across all rows) and see if it finds the same name and if it does then the consecutive rows would be highlighted a yellow colour? The same names might be in row 1 to 4 or it could be in rows 7 and 8 or any other consecutive rows.
The names would be in Column G.
Would really appreciate any help.
Somehow, above code didn't work. In that case, you might wanna try this alternative code:
Sub ColGYellow()
maxrow = ActiveSheet.UsedRange.Rows.Count
For i = 1 To maxrow Step 1
If i <> maxrow Then
If Cells(i, 7).Value = Cells(i + 1, 7).Value Then
Cells(i + 1, 7).Interior.Color = 65535
End If
End If
Next
End Sub
@KiMputer
Maybe because the formula must be a local formula.
On my computer, it works perfectly
Maybe because the formula must be a local formula.
On my computer, it works perfectly
ASKER
Thanks for the replies,
Rgonzo1971: I tried your code but it doesn't seems to work correctly, ie. for the first lot of 3 duplicate rows, it highlighted all 3 rows, for the second lot of 2 duplicate rows, it highlighted the second one but not the first, then for the next set of 2 duplicate rows, it highlighted all 2 rows. In another row, it highlighted a name which was not a duplicate.
Kimputer: I tried your code and it highlights all duplicates except the first one. I wondered is it possible for the code to highlight all the rows including the first one?
Thank you both so much for the code.
Rgonzo1971: I tried your code but it doesn't seems to work correctly, ie. for the first lot of 3 duplicate rows, it highlighted all 3 rows, for the second lot of 2 duplicate rows, it highlighted the second one but not the first, then for the next set of 2 duplicate rows, it highlighted all 2 rows. In another row, it highlighted a name which was not a duplicate.
Kimputer: I tried your code and it highlights all duplicates except the first one. I wondered is it possible for the code to highlight all the rows including the first one?
Thank you both so much for the code.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks again - both blocks of code work great.
pls try
Open in new window
Regards