2003 - Highlight duplicates, but leave one alone

[samboytor]

I'm looking for a way to highlight duplicate entries but I want to leave one un-highlighted. For example: if I have 5 of the same entry, 4 are highlighted.

 

[Marcelo Branco]

Maybe

Select the entire sheet (all cells) and

CF (Excel 2007 or higher)
=COUNTIF(A1:$X$1048576,A1)>1

All the duplicates instances will be highlighted, except the last one

M.

 

[jasonb75]

Maybe

Select the entire sheet (all cells) and

CF (Excel 2007 or higher)
=COUNTIF(A1:$X$1048576,A1)>1

All the duplicates instances will be highlighted, except the last one

M.

Sorry to say this Marcelo, I made it fail

A1:A5,B1:B4,C1:C3,D1:D2 and E1 all contain the word "Test"

Now I'm going to sneak off and return to the problem in #18

 

[Marcelo Branco]

Sorry to say this Marcelo, I made it fail

A1:A5,B1:B4,C1:C3,D1:D2 and E1 all contain the word "Test"

Now I'm going to sneak off and return to the problem in #18

Yes, you are right.... and

M.

 

[jasonb75]

Ok, A1,B1,A2 problem now fixed, also it now checks before and after instead of just before and no longer highlights empty cells if you delete an entry.

I've left it as before to call Robert's code in the event of multiple entries being made together, i.e. as result of copy / paste. With the changes I've made the need to copy / paste to double check is no longer there, but my code is only written for testing single cells.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then
    HighlightDups
Else
    If Target <> "" And Cells.Find(What:=Target.Value, after:=Target.Offset(1, 0), LookIn:=xlValues, LookAt:= _
            xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False _
            , SearchFormat:=False).Address <> Target.Address Then
    Target.Interior.Color = RGB(0, 255, 0)
    Else: Target.Interior.Pattern = xlNone
    End If
End If
End Sub

 

[jasonb75]

Yes, you are right.... and

M.

It only counts Below and right of the reference cell.

In C3 your formula would be

=COUNTIF(C3:$X$1048576,C3)>1 meaning a lot of potential duplicates are missed.

 

[samboytor]

Thanks! That should be good enough.

 

[mikerickson]

A formula like this will highlight all the duplicates (except the last) in the range A1:C10.

=MAX(($A$1:$C$10=A1)*(ROW($A$1:$C$10)+COLUMN($A$1:$C$10)/100))>(ROW(A1)+COLUMN(A1)/100)