Excel VBA Highlight Duplicates and Duplicate between Range

[decent_boy]

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

Book5
	C	D	E
1	Numbers	Remarks	Duplication
2	12345		1 Duplication
3	23456	This number is duplicated between C6 Cell	2 Duplication
4	32456
5	12345		1 Duplication
6	23455-23458		Total Duplicated in this range 3
7	23457	This number is duplicated between C6 Cell	3 Duplication
8	34567
9	23455	This number is duplicated between C6 Cell	3 Duplication
Sheet1

 

[decent_boy]

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

 

[shinigamilight]

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]

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]

Can anybody solve it please