Help conditional formatting not working

deanl33069

Board Regular
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
 

deanl33069

Board Regular
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

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

10089677500277200000000000000000000000000
3333
10089677500277200000000000000000000000000
10089677500277200000000000000000000000000
10089677500277200000000000000000000000000
190027182302053000000000000000000000000000000
190027182302053000000000000000000000000000000
190027182302053000000000000000000000000000000

<colgroup><col style="mso-width-source:userset;mso-width-alt:12763;width:262pt" width="349"> </colgroup><tbody>
</tbody>
 

Fluff

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

Fluff

MrExcel MVP, Moderator
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

Well-known Member
If you are willing to use VBA then this should work

Code:
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
 

Scott T

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

Code:
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
 

Some videos you may like

This Week's Hot Topics

Top