Improve company productivity with a Business Account.Sign Up

Status: Solved
Priority: Medium
Security: Public
Views: 5814
Last Modified: 2014-02-10

Highlighting duplicate values in Excel in different colors

Column D in my spreadsheet lists chapter titles. The spreadsheet tracks every time a chapter is being revised, so there are a number of duplicate values. I would to have the spreadsheet automatically highlight duplicate chapter titles in column D with its own unique color. Any chapter titles not duplicated would not have any color. For example:

Column D
The Cat Came Back (color # 1)
The Dog Sits Still (color # 2)
Charley Goes to Camp (unique, so no color)
The Cat Came Back (color # 1)
Trixie Likes Icecream (unique, so no color)
The Dog Sits Still (color # 2)
The Dog Sits Still (color # 2)
The Cat Came Back (color # 1)

Thanks in advance for help with this...

Cheers,
Andrea

Andreamary

Asked: 2014-02-06

Advertise Here

1 Solution

LVL 28

MacroShadowCommented: 2014-02-06

Try this:

Sub Find_Duplicate_Entry()
    Dim cel As Variant
    Dim myrng As Range
    Dim clr As Long
    Set myrng = Range("A1:A" & Range("A65536").End(xlUp).Row)
    myrng.Interior.ColorIndex = xlNone
    clr = 3
    For Each cel In myrng
        If Application.WorksheetFunction.CountIf(myrng, cel) > 1 Then
            If WorksheetFunction.CountIf(Range("A1:A" & cel.Row), cel) = 1 Then
                cel.Interior.ColorIndex = clr
                clr = clr + 1
            Else
                cel.Interior.ColorIndex = myrng.Cells(WorksheetFunction.Match(cel.Value, myrng, False), 1).Interior.ColorIndex
            End If
        End If
    Next
End Sub

Open in new window

AndreamaryAuthor Commented: 2014-02-10

Thanks for the quick response. Can I just get your guidance on putting this code into my workbook, as this it not an area I have much experience in...

I tried to enter the code above by right-mouse clicking on the relevant tab and clicking on "View Code", and then pasting the code into a blank window, at the top of which it says "General" on the left, and "Find_Duplicate_Entry" on the right. I flipped back to the spreadsheet view and saved the spreadsheet, but don't see any results in column D. I suspect I'm doing something wrong at my end.

Thanks,
Andrea

LVL 28

MacroShadowCommented: 2014-02-10

Seems like you added the code correctly, now you just have to run the macro.

Hit Alt+F8 to bring up the macro dialog, chose the macro and click run.

AndreamaryAuthor Commented: 2014-02-10

It worked...cool!

A couple of things...

The macro ran on Column A, not D...how do I change it to column D?

My preference would be for this highlighting to be "on the fly" as new rows are added, rather than having to run a macro...is this possible?

Is there a way I can reference a custom color palette instead of the default colors so it's a little easier on the eyes?

Thanks!
Andrea

LVL 28

MacroShadowCommented: 2014-02-10

The macro ran on Column A, not D...how do I change it to column D?

Change this:

Set myrng = Range("A1:A" & Range("A65536").End(xlUp).Row)

Open in new window

To this:

Set myrng = Range("D1:D" & Range("D65536").End(xlUp).Row)

Open in new window

My preference would be for this highlighting to be "on the fly" as new rows are added, rather than having to run a macro...is this possible?

This will work but be forewarned, it is not very efficient since it will process all records each time a change is made.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cel As Variant
    Dim myrng As Range
    Dim clr As Long
    If Not Intersect(Target, Range("D1:D65536")) Is Nothing Then
        Set myrng = Range("D1:D" & Range("D65536").End(xlUp).Row)
        myrng.Interior.ColorIndex = xlNone
        clr = 3
        For Each cel In myrng
            If Application.WorksheetFunction.CountIf(myrng, cel) > 1 Then
                If WorksheetFunction.CountIf(Range("A1:A" & cel.Row), cel) = 1 Then
                    cel.Interior.ColorIndex = clr
                    clr = clr + 1
                Else
                    cel.Interior.ColorIndex = myrng.Cells(WorksheetFunction.Match(cel.Value, myrng, False), 1).Interior.ColorIndex
                End If
            End If
        Next
    End If
End Sub

Open in new window

Is there a way I can reference a custom color palette instead of the default colors so it's a little easier on the eyes?

Yes, but that is much more complicated and therefore warrants a separate question.

AndreamaryAuthor Commented: 2014-02-10

Thanks for the quick response and the explanation on points #2 and #3. I tried the new code above so it works 'on the fly' (pasting the code in the same way as I did originally), but it doesn't seem to be taking effect. I did notice that in line 11, there is still a reference to A1:A, so tried changing that to "D1:D" but that didn't do the trick.

Any advice?

Thanks

AndreamaryAuthor Commented: 2014-02-10

An update...

With line # 11 revised as described above, I happened to make a revision to a new row in the spreadsheet, and that suddenly triggered the duplicates in Column D to show...success, thanks!

One last thing if possible — is there any way to prevent my heading row for Column D from being included: ie., for the code to start at D2 instead of D1?

LVL 28

MacroShadowCommented: 2014-02-10

Change all occurrences of D1 to D2.

AndreamaryAuthor Commented: 2014-02-10

Terrific...thanks very much!

Question has a verified solution.

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

Expand your skills with this month's free Premium course.

Highlighting duplicate values in Excel in different colors

Join & Write a Comment Already a member? Login.

Featured Post