estatefinds
04-10-2016, 11:01 AM
I run this code to high duplicates but its seems to be highlighting data right off without have duplicates. so in column E I have five number combinations
1-2-3-4-5 like this. then I place other data like this that will most certainly be found as a duplicate. so every time I run this, it highlights when there is no duplicate to be found , I know this cause When I do a find select search it only finds one unique five number combination, so how can this highlight in yellow if there is no duplicate? its needs to high light the exact match some where else in the data. so if there is another combination 1-2-3-4-5 then it should highlight, but not highlight only unless there is an exact match.
Sub Highlight_Duplicates_In_Column_E()
Dim lastRow As Long
Dim matchFoundIndex As Long
Dim iCntr As Long
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
End With
lastRow = Cells(Rows.Count, "E").End(xlUp).Row
For iCntr = 2 To lastRow
If Cells(iCntr, 5) <> "" Then
matchFoundIndex = _
WorksheetFunction.Match(Cells(iCntr, 5), Range("E2:E" & lastRow), 0)
If iCntr <> matchFoundIndex Then
Cells(iCntr, 5).Interior.Color = vbYellow
End If
End If
Next
Columns("E").EntireColumn.AutoFit
With Application
.DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Sub
1-2-3-4-5 like this. then I place other data like this that will most certainly be found as a duplicate. so every time I run this, it highlights when there is no duplicate to be found , I know this cause When I do a find select search it only finds one unique five number combination, so how can this highlight in yellow if there is no duplicate? its needs to high light the exact match some where else in the data. so if there is another combination 1-2-3-4-5 then it should highlight, but not highlight only unless there is an exact match.
Sub Highlight_Duplicates_In_Column_E()
Dim lastRow As Long
Dim matchFoundIndex As Long
Dim iCntr As Long
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
End With
lastRow = Cells(Rows.Count, "E").End(xlUp).Row
For iCntr = 2 To lastRow
If Cells(iCntr, 5) <> "" Then
matchFoundIndex = _
WorksheetFunction.Match(Cells(iCntr, 5), Range("E2:E" & lastRow), 0)
If iCntr <> matchFoundIndex Then
Cells(iCntr, 5).Interior.Color = vbYellow
End If
End If
Next
Columns("E").EntireColumn.AutoFit
With Application
.DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Sub