Avatar of ADRIANA P
ADRIANA P
Flag for United States of America asked on

Need conditional formating when doubles

have an list of numbers
but i need identifu the doublesas i type in

like the example  here
3num-doubles-examples.xlsx
Microsoft OfficeSpreadsheetsMicrosoft ExcelVBA

Avatar of undefined
Last Comment
Shums Faruk

8/22/2022 - Mon
ADRIANA P

ASKER
sorry my keyboard give me some hard times
some times

I have an list of numbers
but i need identify  the doubles as i type in
Shums Faruk

Hi Adriana,

Please find attached. Its automated with VBA.
3num-doubles-examples_v1.xlsm
Shums Faruk

The code used is:
Sub HighlightDups()

    Dim wstMySheet As Worksheet
    Dim clnUniqueValues As New Collection
    Dim rngCell As Range
    Dim lngEndRow As Long
    
    Set wstMySheet = Sheets("Sheet1") 'Sheet containing data. Change to suit.
    
    lngEndRow = wstMySheet.Range("B:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    Application.ScreenUpdating = False
    
    For Each rngCell In wstMySheet.Range("B5:B" & lngEndRow) 'Assumes the data is in Col B (starting at Row 5) of 'wstMySheet'. Change to suit.
        On Error Resume Next 'OK to ignore 'This key is already associated with an element of this collection' error message.
            clnUniqueValues.Add rngCell, CStr(rngCell)
            If Err.Number <> 0 Then
                wstMySheet.Range("B" & rngCell.Row).Interior.Color = RGB(255, 255, 0) 'Yellow. Change to suit.
                wstMySheet.Range("B" & rngCell.Row).Font.Color = RGB(156, 0, 6) 'Dark Red
                wstMySheet.Range("B" & rngCell.Row).Font.Bold = True
            End If
            Err.Clear
        On Error GoTo 0
    Next rngCell
    
    Application.ScreenUpdating = True
    
    'Remove objects from memory
    Set wstMySheet = Nothing
    Set clnUniqueValues = Nothing

End Sub

Open in new window

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ADRIANA P

ASKER
Shums !! fast response always like that from you my  good friend !!

but still dont; work for me because

I need as i type in
and have to be all the list numbers in the list already in
Shums Faruk

After typing just press that button Highlight Duplicates. It will highlight.
I have changed the code, it will reset the old duplicates and highlight only latest duplicates.
Sub HighlightDups()

    Dim wstMySheet As Worksheet
    Dim clnUniqueValues As New Collection
    Dim rngCell As Range
    Dim lngEndRow As Long
    
    Set wstMySheet = Sheets("Sheet1") 'Sheet containing data. Change to suit.
    
    lngEndRow = wstMySheet.Range("B:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    Application.ScreenUpdating = False
    
    
    For Each rngCell In wstMySheet.Range("B5:B" & lngEndRow) 'Assumes the data is in Col B (starting at Row 5) of 'wstMySheet'. Change to suit.
        On Error Resume Next 'OK to ignore 'This key is already associated with an element of this collection' error message.
            clnUniqueValues.Add rngCell, CStr(rngCell)
            wstMySheet.Range("B" & rngCell.Row).Interior.Pattern = xlNone
            wstMySheet.Range("B" & rngCell.Row).Font.ColorIndex = xlAutomatic
            wstMySheet.Range("B" & rngCell.Row).Font.Bold = False
            If Err.Number <> 0 Then
                wstMySheet.Range("B" & rngCell.Row).Interior.Color = RGB(255, 255, 0) 'Yellow. Change to suit.
                wstMySheet.Range("B" & rngCell.Row).Font.Color = RGB(156, 0, 6) 'Dark Red
                wstMySheet.Range("B" & rngCell.Row).Font.Bold = True
            End If
            Err.Clear
        On Error GoTo 0
    Next rngCell
    
    Application.ScreenUpdating = True
    
    'Remove objects from memory
    Set wstMySheet = Nothing
    Set clnUniqueValues = Nothing

End Sub

Open in new window

3num-doubles-examples_v2.xlsm
ADRIANA P

ASKER
Shums have to be all the numbers
not
just one or the last one
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Shums Faruk

If you want to highlight duplicates manually then follow below steps:
1. Select the range you want to highlight
2. Navigate to Home/Conditional Formatting/Highlight Cells Rule/Duplicate Values
3. Default color would be Light Red Fill with Dark Red Text, If you want custom color, click the drop-down key (right after Color selection)
4. Custom Format/Font/Color (Automatic) change the color you want, Fill the color you want
5. Press OK
6. Press OK.
And you are done
Shums Faruk

Please find attached...try to type any duplicate numbers, it will be highlighted
3num-doubles-examples_v3.xlsx
ADRIANA P

ASKER
ok im interest in doubles numbers
not duplicates numbers
 numbers like
552
616
828
225
but the doubles numbers can be in any position
could be

552
225
252
522
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Shums Faruk

Oopps....Sorry for misunderstanding.
ASKER CERTIFIED SOLUTION
Shums Faruk

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ADRIANA P

ASKER
Yes ! that's im talking about i!!
great job !
ADRIANA P

ASKER
Great Job !
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Shums Faruk

You're Welcome Adriana! Glad I was able to help