VBA appears to be missing something please help!!

[estatefinds]

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

[PAB]

Hi Dennis,

I assume that you are talking about the code that I wrote for you in the post...

http://www.vbaexpress.com/forum/showthread.php?55689-Need-vba-macro-to-highlight-duplicates-in-a-column

What you need to do is to highlight column W and format the FILL as NO COLOUR.
Preferably though, add some extra code to do this in the code that I wrote you, that way it will always start with a clean column.

I hope this helps!

[estatefinds]

ok I did do that meanng i i selected the column W and clicked on no fill then ran it and it does the same thing highlights non duplicates, it starts highlighted from row 2 down. but its not highlighting the goal ; highlighting duplicated only. so it has to match the entire cell contents in order to be a duplicate.

[PAB]

I don't understand why because the previous code I wrote and posted for you worked!

Can you post the WorkBook please?

[estatefinds]

ok here it is so I put exact combinations above and below but in the first rows I left out a few to see if it would highlight the non duplicates and it did it again.

[PAB]

Give this a go...


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 = 1 To lastRow
If Cells(iCntr, 5) <> "" Then
matchFoundIndex = _
WorksheetFunction.Match(Cells(iCntr, 5), Range("E1: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

I hope this helps!

[estatefinds]

ok great it works!!! is there any way at all, i know you worked very hard on this, but is there a way where it highlights the first data instead of the second data below. I ask because the data in my original worksheet will be highlighted based on the data below it. if you can please Thank you

[PAB]

This will highlight non-duplicates...


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 = 1 To lastRow
If Cells(iCntr, 5) <> "" Then
matchFoundIndex = _
WorksheetFunction.Match(Cells(iCntr, 5), Range("E1: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

I hope this helps!

[estatefinds]

Thank you, but I still need the duplicates here is the file if you can change this for me,
Thank you very much!

so instead of the vales below the first set of values being highlighted it will only be the duplicates in the first set of data.

you see the note in the file.

so same code for duplicate it just highlighting the ones in first data at begining or top of worksheet.

so the data in the first set will get highlighted depending if the duplicate is found in the second set.

ill post as a new post. I just wanted to say you great work!!!! great job on this!!!Thank you again!!!