Excel VBA Filtering out duplicates

Hi
I have a spreadsheet of data that contains duplicates in column A.
I want to loop through column A and for each ID and highlight
all other duplicates in green (and not the instance that I have found)

1
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:
Try macro from this sample
Mark-duplicates.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Pratik MakwanaData AnalystCommented:
you can use Conditional Formatting function for it.
1. Select your whole sheet data with all rows & columns.
2. Go to Conditional Formatting.
3. Select New Rule.
4. Now Select Format only Unique or duplicate values.
5. Now select duplicate at Format All.
6. At Preview click on Format and Select your background/font color and click OK.
7. Click on Final OK at New Formatting Rule popup.

You are done with your work.......................
1.jpg
2.jpg
3.jpg
0
Glenn RayExcel VBA DeveloperCommented:
I assume that:
1) You only want to highlight duplicate values in column A that match a cell selected in column A also.
2) You want this behavior to be dynamic; that is, if you select a new cell, the original highlighting is removed and a new check is done for duplicate values to the new cell.
3) If you select any cell outside of column A, no highlighting will appear.

This Worksheet_Change event (inserted in the sheet module) will do the above:
Option Explicit
Dim valKey As Variant
Dim lngKeyRow As Long
Dim rng As Range
Dim cl As Object
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 1 And Target.Rows.Count = 1 Then
        valKey = Target.Value
        lngKeyRow = Target.Row
        Set rng = Range("A2", Range("a2").End(xlDown))
        For Each cl In rng
            If cl.Row <> lngKeyRow Then
                If cl.Value = valKey Then
                    cl.Interior.Pattern = xlSolid
                    cl.Interior.Color = RGB(0, 255, 0)
                Else
                    cl.Interior.Pattern = xlNone
                End If
            Else
                cl.Interior.Pattern = xlNone
            End If
        Next cl
    Else
        Set rng = Range("A2", Range("a2").End(xlDown))
        For Each cl In rng
            cl.Interior.Pattern = xlNone
        Next cl
    End If
End Sub

Open in new window


Example workbook attached.

Regards,
-Glenn
EE-HighlightDuplicates.xlsm
0
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks very much. The VBA code was what I needed
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.