# Excel VBA Highlight Duplicates and Duplicate between Range



## decent_boy (Dec 18, 2022)

I need a VBA solution to highlight duplicate numbers and duplicate between range.

Book5CDE1NumbersRemarksDuplication2123451 Duplication323456This number is duplicated between  C6 Cell2 Duplication4324565123451 Duplication623455-23458Total Duplicated in this range 3723457This number is duplicated between  C6 Cell3 Duplication834567923455This number is duplicated between  C6 Cell3 DuplicationSheet1


----------



## decent_boy (Dec 18, 2022)

Can anybody please reply that is it possible this kind of highlighting or should i need to explain it more


----------



## shinigamilight (Dec 18, 2022)

*This will convert the ranges into numbers, if you don't want that then don't use this script. Use at your own discretion.*


```
Sub text_to_number()
        
        Dim k, i, j As Integer
        Dim lr, lr2 As Long
        Dim container() As String
        Dim storage As String
        lr = Cells(Rows.Count, 1).End(xlUp).Row
        
        For k = lr To 2 Step -1
        
                If WorksheetFunction.IsText(Range("A" & k)) Then
                    
                  container = Split(Cells(k, 1), "-")
                  
                  string1 = container(0)
                  string2 = container(1)
                  
                  Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = string1
                        j = 1
                  Do Until storage = string2
                        storage = string1 + j
                        Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = storage
                        j = j + 1
                  Loop
                  Cells(k, 1).EntireRow.Delete
                  End If
                  
    Next k
    
    Call text_to_number2

End Sub
```


```
Sub text_to_number2()
                
        Dim k As Integer
        Dim lr As Long
        
        lr = Cells(Rows.Count, 1).End(xlUp).Row
        For k = 2 To lr
        If WorksheetFunction.CountIf(Range("A2:A" & lr), Range("A" & k)) > 1 Then
            Range("A" & k).Interior.Color = vbYellow
        End If
        Next k

End Sub
```


----------



## decent_boy (Dec 18, 2022)

Hi shinigamilight,
Thanks for you reply , but I want it to be highlighted and given duplication count as I mentioned in above example.
It is just like a challenging work


----------



## decent_boy (Dec 18, 2022)

Can anybody solve it please


----------

