PDA

View Full Version : Check FirstName&LastName duplicates



trivrain
12-12-2010, 03:56 PM
Hi,
Am looking for a VB code that allows to highlight duplicates with both duplicate FirstName & LastName - not just either one - and without having to resort to a concataner formula.
Ideally I would like it to prompt the user to select column1 and then column 2 so it it is transportable to any workbook and quicker for the amount of spread I have to sort this week:
Set Column1 = Application.InputBox("Select First Column to Compare", Type:=8)... Set Column2 = Application.InputBox("Select Second Column to Compare", Type:=8)or something flexible of that sort;
and make both FirstName & LastName cells fill red: Column1.Cells(intCell).Interior.Color = vbRed
Column2.Cells(intCell).Interior.Color = vbRedOf course having discovered VBA on saturday, well, erm... any help is appreciated.
t

CatDaddy
07-18-2011, 01:12 PM
Sub HighlightDuplicates()
Dim col As String
Dim cell As Range
Dim lRow As Long
'FINDS LAST ROW IN DATA BASED ON DATA IN COL A (replace with full column if not A)
ActiveWorkbook.Sheets(1).Activate
Range("A1").Activate
For Each cell In Range("A:A")
If cell.Value = Empty Then
lRow = cell.Row - 1
Exit For
End If
Next cell
'CHOOSE YOUR TWO COLUMNS (FIRST NAME, LAST NAME)
col1 = InputBox("First Name column letter to search for duplicate:", , "A")
col2 = InputBox("Last Name column letter to search for duplicate:", , "B")

Dim fName As String, lName As String
Dim i As Long, j As Long
'CHECKS FOR DUPLICATES BUT DOES NOT HIGHLIGHT THE ORIGINAL
For i = 2 To lRow
fName = Cells(i, col1).Text
lName = Cells(i, col2).Text

For j = i To lRow
'TO HIGHLIGHT ORIGINAL AS WELL REMOVE "And j <> i" FROM CONDITIONAL
If Cells(j, col1).Text = fName And Cells(j, col2).Text = lName And j <> i Then
Range(Cells(j, col1), Cells(j, col2)).Select
Selection.Interior.Color = 255
End If
Next j
Next i
End Sub

I tested it and if your names are in the same rows you dont need the inputbox (just set col1 and col2 = the letter of the column, ie. "A", "B", etc.)