asked on

multiple unique values in different columns

Hello,

I would like to highlight any cell that has the same value but I would like the different values to have the same color. Also I need a macro where it can run for multiple columns at the same time. I found one that the range would be for only one column at a time.

This is what I need done:

Name       City        State
Mike       NYC        NY
Amanda       Las Vegas      NV
Peter       Miami        FL
Mike       Atlanta        GA
Amanda       Las Vegas      NV

The word Mike would be green for example, Amanda would be yellow. On the city column Las Vegas would be an unique color. On the State one only NY would be highlighted.

This is the macro I found but it only runs on one column at a time:

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

Thanks

Rgonzo1971

Hi,

pls try

Sub Find_Duplicate_Entry()
     Dim cel As Variant
     Dim myrng As Range
     Dim clr As Long
     clr = 3
     For idx = 1 To 3  ' number of columns
        Set myrng = Range(Cells(1, idx), Cells(Rows.Count, idx).End(xlUp))
        myrng.Interior.ColorIndex = xlNone
        For Each cel In myrng
            If Application.WorksheetFunction.CountIf(myrng, cel) > 1 Then
                If WorksheetFunction.CountIf(myrng.Resize(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
    Next
 End Sub

Open in new window

Neo F

ASKER

It worked but only on Column A. I need to either let me select the range that should be analyzed (I.e. from A1 - Y50) or it should apply to all columns.

Neo F

ASKER

I changed the number of columns to 24 but somehow it stopped on 21 and it gave me the following error:

Run-Time error '9":
Subscript out of range

When I click on debug it highlights the following line:
cel.Interior.ColorIndex = clr

I believe,but I do not know if this is the problem, is that excel does not have other different colors and it stopped when all of them were utilized.
If the match belongs to a different column I don't mind if the color repeats as long as they are on different columns

Neo F

ASKER

Even better it would be if the unique color would be specified on Column A and then for color B,C,D etc they would mirror the color on color A on their same row. I.E

Column A row 1 is red so for any duplicate that is on Column B row 1 it would also be red. The same for C1, D1, E1, etc.

SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Neo F

ASKER

Thank you Shums for your response. When I tried your code it gave me an error that said:
Run-Time error 91:
Object variable or with block variable not set

if I click on debug it shows me the line:
If myrng.Find(what:=cell, lookat:=xlWhole, MatchCase:=False, after:=lastCell).Address = cell.Address Then

Shums Faruk

What range you set for lastcell? in my code its Col A, which is yours?

Rgonzo1971

then try

Sub Find_Duplicate_Entry()
     Dim cel As Variant
     Dim myrng As Range
     Dim clr As Long

     For idx = 1 To Cells(Columns.Count, 1).End(xlToLeft).Column  ' number of columns
        clr = 3
        Set myrng = Range(Cells(1, idx), Cells(Rows.Count, idx).End(xlUp))
        myrng.Interior.ColorIndex = xlNone
        For Each cel In myrng
            If Application.WorksheetFunction.CountIf(myrng, cel) > 1 Then
                If WorksheetFunction.CountIf(myrng.Resize(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
    Next
 End Sub

Open in new window

Neo F

ASKER

Shums - My data is from column A - Y

Rgonzo1971. I tried yours but it only colored Column A. It did not color the remaining columns

Shums Faruk

Neo,

Please see attached dummy, it works fine here.
Highlight-Duplicates-Multiple-Colum.xlsm

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial