Help conditional formatting not working

[deanl33069]

I have a bunch of unique numbers in cells a2:a310. they are from a barcode scanner.
the first 10 numbers are the same and the rest are different . when i highlight column A and set conditional formatting to duplicate are red , all cells turn red even though the numbers are different . I have tried different formats like Text, numbers,custom ect nothing works.....help

 

[Fluff]

What sort of values do you have in col A, can you give some examples?

 

[deanl33069]

010089677500277211180619310100181421091643
3333
010089677500277211180619310200181421091636


010089677500277211040719310200181421002815
010089677500277211040719310200181421002803
0190027182302053320100010011190715210100445114
0190027182302053320100010011190715210100445112
0190027182302053320100010011190715210100444943


0190027182302053320100010011190715210100436274
0190027182302053320100010011190715210100436082
0190027182302053320100010011190715210100436083
0190027182302053320100010011190715210100436287
0190027182302053320100010011190715210100436279
0190027182302053320100010011190715210100444294
0190027182302053320100010011190715210100436278
0190027182302053320100010011190715210100444150
0190027182302053320100010011190715210100444148
0102696200002371320200194511190809210222100192

all except the 3333 highlight as duplicates.

 

[Fluff]

The CF is presumably seeing those values as numbers, in which case it only "sees" the first 15 significant characters, ie

<colgroup><col style="mso-width-source:userset;mso-width-alt:12763;width:262pt" width="349"> </colgroup><tbody>
[TD="class: xl65, width: 349, align: right"]10089677500277200000000000000000000000000[/TD]

[TD="class: xl65, align: right"]3333[/TD]

[TD="class: xl65, align: right"]10089677500277200000000000000000000000000[/TD]

[TD="class: xl65"][/TD]

[TD="class: xl65"][/TD]

[TD="class: xl65, align: right"]10089677500277200000000000000000000000000[/TD]

[TD="class: xl65, align: right"]10089677500277200000000000000000000000000[/TD]

[TD="class: xl65, align: right"]190027182302053000000000000000000000000000000[/TD]

[TD="class: xl65, align: right"]190027182302053000000000000000000000000000000[/TD]

[TD="class: xl65, align: right"]190027182302053000000000000000000000000000000[/TD]

</tbody>

 

[Fluff]

Unfortunately, I don't know how to stop that.

 

[Fluff]

Ok select A1 to the last used row in column A & use
=AND(SUMPRODUCT(--(A$1:A$23=A1))>1,A1<>"")

Change value in red to the last used row

 

[Scott T]

If you are willing to use VBA then this should work

Sub checkdups()
Dim rng As Range
Set rng = Range("A2:A310")
For Each cell In rng
    For Each mcell In rng
        If cell = mcell And cell <> "" Then mycount = mycount + 1
    Next mcell
    
    If mycount > 1 Then
    cell.Interior.ColorIndex = 3 'change to how you want to format
    End If
    
    mycount = 0
Next cell
End Sub

 

[deanl33069]

It works but does not update automatically.

 

[Fluff]

Who are you talking to?
Me or Scott T

 

[Scott T]

If you want code to run automatically you can use a change event.
This should be put in the sheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A2:A310")) Is Nothing Then
    Dim rng As Range
    
    Set rng = Range("A2:A310")
    
    For Each cell In rng
        For Each mcell In rng
            If cell = mcell And cell <> "" Then mycount = mycount + 1
        Next mcell
        
        If mycount > 1 Then
        cell.Interior.ColorIndex = 3 'change to how you want to format
        End If
        
        mycount = 0
    
    Next cell
End If
End Sub