Solved

Highlighting duplicate values in Excel in different colors

Posted on 2014-02-06

1 solution

4,456 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

Comment

Question by:Andreamary

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

9 Comments

LVL 27

Expert Comment

by:MacroShadow

ID: 398391932014-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

Author Comment

by:Andreamary

ID: 398476182014-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 27

Expert Comment

by:MacroShadow

ID: 398476312014-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.

Author Comment

by:Andreamary

ID: 398478012014-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 27

Expert Comment

by:MacroShadow

ID: 398478532014-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.

Author Comment

by:Andreamary

ID: 398480152014-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

Author Comment

by:Andreamary

ID: 398480782014-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 27

Accepted Solution

by:MacroShadow

MacroShadow earned 500 total points

ID: 398481952014-02-10

Change all occurrences of D1 to D2.

Author Closing Comment

by:Andreamary

ID: 398482192014-02-10

Terrific...thanks very much!

Featured Post

Question has a verified solution.

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

Freeze Panes feature

Article by: Rob

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.

Microsoft Excel

Dynamic Data Vaildation

Article by: Rob

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.

Creating a Cool Heat Map in Excel

Video by: AnnieCushing

This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

Microsoft Excel

Creating an Interactive Chart for Marketers in Excel

Video by: AnnieCushing

This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.