?
Solved

Highlighting duplicate values in Excel in different colors

Posted on 2014-02-06
9
Medium Priority
?
5,271 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 28

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 28

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
Industry Leaders: 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!

 

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 28

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 28

Accepted Solution

by:
MacroShadow earned 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
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…

621 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