Help conditional formatting not working

deanl33069

Board Regular
Joined
May 2, 2019
Messages
120
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
What sort of values do you have in col A, can you give some examples?
 
Upvote 0
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.
 
Upvote 0
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>
 
Upvote 0
Unfortunately, I don't know how to stop that.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Who are you talking to?
Me or Scott T
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top