Color duplicated cell to an interior colour

[mmldogs]

Hello
I have found code that identifies a duplicate in a single column . Instead of writing " duplicate' in column 2 when duplicate found I would like the identified duplicate higlighted in yellow in column 1
Newbee to vba got FALSE as value and compiling error

Can someone assist

Sub sbFindDuplicatesInColumn()
    Dim lastRow As Long
    Dim matchFoundIndex As Long
    Dim iCntr As Long
    lastRow = Range("A65000").End(xlUp).Row

    For iCntr = 1 To lastRow
    If Cells(iCntr, 1) <> "" Then
        matchFoundIndex = WorksheetFunction.Match(Cells(iCntr, 1), Range("A1:A" & lastRow), 0)
        If iCntr <> matchFoundIndex Then
            Cells(iCntr, 2) = "Duplicate"
       End If
    End If
    Next
End Sub

 

[mrshl9898]

Like this?

Sub sbFindDuplicatesInColumn()
    Dim lastRow As Long
    Dim matchFoundIndex As Long
    Dim iCntr As Long
    lastRow = Range("A65000").End(xlUp).Row

    For iCntr = 1 To lastRow
    If Cells(iCntr, 1) <> "" Then
        matchFoundIndex = WorksheetFunction.Match(Cells(iCntr, 1), Range("A1:A" & lastRow), 0)
        If iCntr <> matchFoundIndex Then
            Cells(iCntr, 1).Interior.Color = 65535
       End If
    End If
    Next
End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Try this with a copy of your workbook to see if it suits your purpose.

Sub Find_Dupes()
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    .FormatConditions.Add Type:=xlExpression, Formula1:="=MATCH(A2,A$1:A1,0)"
    .FormatConditions(1).Interior.Color = vbYellow
  End With
End Sub

 

[mmldogs]

hey thanks for the prompt reply
works well for column A . If I were to apply this to a range in column E
Would the code be ?

trying to understand and apply .
All profile updated

Sub Find_Dupes()
  With Range("E3", Range("E" & Rows.Count).End(xlUp))
    .FormatConditions.Add Type:=xlExpression, Formula1:="=MATCH(E3,E$3:E3,0)"   ' or  zero replace 0 with 5 because its the fifth column 
    .FormatConditions(1).Interior.Color = vbYellow
  End With
End Sub

 

[Peter_SSs]

If I were to apply this to a range in column E
Would the code be ?

With Range("E3", Range("E" & Rows.Count).End(xlUp))

The blue should be the second cell of the actual data you are trying to check for duplicates. Logic is that you cannot get a duplicate with the first value so no need to do anything with that cell. So E3 would be correct if you had a heading in row 1 and data started in row 2.

Formula1:="=MATCH(E3,E$3:E3,0)"

This is not correct. Those red 3s should be 1 less than the blue value. Logic is that we are checking above the 'current' cell to see if that value already exists. Note that in my original code it was:

Formula1:="=MATCH(A2,A$1:A1,0)"

.. and thanks for updating your profile.

 

[mmldogs]

Thank you so much

 

[Peter_SSs]

Thank you so much

You're welcome.