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.

 

[taurean]

The formula I have stated will work column wise. All you need to do is copy down the formatting to the bottom most cell. There is no reason why it won't work. It will not highlight across column cells. See below:

Excel Workbook
	A
1	1
2	1
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	1. / Formula is =COUNTIF($A$1:A1,A1)>1	Abc
A2	1. / Formula is =COUNTIF($A$1:A2,A2)>1	Abc

 

[samboytor]

Yes, your new formula will work down a column. I am looking for something to work across an entire sheet.

 

[jasonb75]

I think taurean and myself have (incorrectly) interpreted your problem differently to Robert.

How are you entering the data to the sheet? Typing it, formula results, copy and paste, or something else?

Is there any consistancy to the direction in which the data is entered or the number of rows / columns used.

I ask because the code Robert has given you is ideal for doing an entire range, but when you add another entry to that range it would have to re-evaluate the entire range again, other methods could do it per cell as data is added / changed.

 

[samboytor]

I am typing the data in and it is text.

Example: Form 201, PR-0011, WI8.3.0-01

No, there is no consistancy (data is entered down columns and across rows). The number of rows and columns is undetermined.

 

[jasonb75]

See if this works for you,

Entering data in say D25 would compare that entry to all data in columns A:C and entries in D1:D24, but would miss D26:Dxx and columns E:xyz.

This would allow you to check each entry as you go, without the delay you would get from Roberts code if you had a lot to process.

As a "double check" clause, I've included a call to Roberts code from mine (so you will need to keep that code in the module), a count of multiple changes will execute Roberts code instead of mine, a quick and easy way to trigger this is to copy 2 cells and paste them over themselves. (i.e. Shift + any cursor key, Ctrl c, Ctrl v), so a fairly quick action.

This code needs to be in the worksheet module, (right click the sheet tab, then view code)

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

 

[samboytor]

That sounds like what I'm looking for, but I don't think I'm using your code properly (I'm not seeing any different results).

Do I put your code in the same module as Robert's code? If so, which one goes first?

 

[jasonb75]

No, Robert's code needs to stay in the regualr module, my code needs to go into the module that is specific to the worksheet.

Right click the sheet tab in excel (i.e. the bit at the bottom that says "Sheet1" or whatever you sheet is called), then click "View code", that will take you to the correct place.

If that doesn't work try running this code in a regular module to make sure event handling is enabled.

Sub Enable()
    Application.EnableEvents = True
End Sub

 

[samboytor]

Nice. That works for me.

I hate to be picky, but why doesn't the hilighting hold true for A1 & A2, and sometimes B1?

 

[mikerickson]

You could use this condition to highlight duplicates in a column, leaving the first one not highlited.

=(MATCH(A1, A:A,0)<>ROW(A1))

 

[jasonb75]

You could use this condition to highlight duplicates in a column, leaving the first one not highlited.

=(MATCH(A1, A:A,0)<>ROW(A1))

I think you made the same interpretation error as I did earlier mike, the record needs to be compared to multiple rows and columns, not just 1.

I couldn't see any way of doing that without arrays.