Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Highlighting duplicate values in Excel in different colors

Posted on 2014-02-06
9
Medium Priority
?
4,991 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

916 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