Solved

Highlighting duplicate values in Excel in different colors

Posted on 2014-02-06
9
4,098 Views
Last Modified: 2014-02-10
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
0
Comment
Question by:Andreamary
  • 5
  • 4
9 Comments
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39839193
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

0
 

Author Comment

by:Andreamary
ID: 39847618
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
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39847631
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.
0
Simple, centralized multimedia control

Watch and learn to see how ATEN provided an easy and effective way for three jointly-owned pubs to control the 60 televisions located across their three venues utilizing the ATEN Control System, Modular Matrix Switch and HDBaseT extenders.

 

Author Comment

by:Andreamary
ID: 39847801
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
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39847853
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.
0
 

Author Comment

by:Andreamary
ID: 39848015
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
0
 

Author Comment

by:Andreamary
ID: 39848078
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?
0
 
LVL 27

Accepted Solution

by:
MacroShadow earned 500 total points
ID: 39848195
Change all occurrences of D1 to D2.
0
 

Author Closing Comment

by:Andreamary
ID: 39848219
Terrific...thanks very much!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question