Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

Solved

Highlighting duplicate values in Excel in different colors

Posted on 2014-02-06

1 solution

Medium Priority

4,747 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 2000 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

Excel - Did You Know !

Article by: Shums

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…

Custom Document Properties in Excel

Article by: Martin

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.

How to use the IF function in Excel

Video by: Bob

Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

Microsoft Excel

Enumerating Prime Numbers in Excel / VBA

Video by: Ed

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…