Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5814
  • Last Modified:

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
0
Andreamary
Asked:
Andreamary
  • 5
  • 4
1 Solution
 
MacroShadowCommented:
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
 
AndreamaryAuthor Commented:
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
 
MacroShadowCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
AndreamaryAuthor Commented:
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
 
MacroShadowCommented:
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
 
AndreamaryAuthor Commented:
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
 
AndreamaryAuthor Commented:
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
 
MacroShadowCommented:
Change all occurrences of D1 to D2.
0
 
AndreamaryAuthor Commented:
Terrific...thanks very much!
0
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.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now