Enter duplicates multiples columns Highlighting with different colors with VBA

[Goalexcel]

Data in sheet1 start A2 until J or more columns need to highlight with different colors.
Each color represent the number duplicate that start in column N and more columns.
When number is repeated for third time will change the color with the new color.
Code need to be flexible every time enter a new duplicate, and run with active workbook, and active worksheet
Add comments with text: Repeated N, O
If possible, use light colors.

Please see below image with result looking for

 

[bebo021999]

Sub test()
Application.ScreenUpdating = False
Dim rB As Range
Dim k, Lr, Lc, color As Long
Dim Str, c As String
Dim Dic As Object
Set Dic = CreateObject("Scripting.Dictionary")
Set rB = Range("N2").CurrentRegion
Lr = Cells(Rows.Count, 1).End(xlUp).Row
Lc = Cells(Lr, Columns.Count).End(xlToLeft).Column
With Range("A1", Cells(Lr, Lc))
.Interior.color = xlNone
.ClearComments
End With
    For Each cell In rB
        color = cell.Interior.color 'read the Color
        Str = "@" & cell.Value & "@" & Cells(1, cell.Column)
        If Not Dic.exists(Str) And cell <> "" Then
        Dic.Add Str, color                              ' create a unique list: @13250@N,...,@6255@N ,@6255@O
        End If
    Next
    For Each cell In Range("A1", Cells(Lr, Lc))
        c = ""
        k = 0
        For Each Key In Dic.keys
            If InStr(1, Key, "@" & cell & "@") > 0 Then 'loop each cell, check if value exist in uniquelist
            cell.Interior.color = Dic.Item(Key)         ' then write color down
            c = c & Split(Key, "@")(2) & ","            'read duplicate value then write column header
            k = k + 1
            End If
        Next
        If k > 1 Then cell.AddComment "Repeat " & c 'write the comment
    Next
Application.ScreenUpdating = False
End Sub

 

[Goalexcel]

@bebo021999 Thank you for your replay. Please kindly check the below file, after I run the code only appear comments, but not colors.

Transfer - Dropbox

Dropbox is a free service that lets you bring your photos, docs, and videos anywhere and share them easily. Never email yourself a file again!

www.dropbox.com

 

[bebo021999]

The code is trying to read color from N:R range.
There is no background color set to that range.
Try to set color as display in your 1st image then it should works..

 

[Goalexcel]

Thank you for your time and outstanding work @bebo021999 ..wow Code works like a charm!!!

 

[bebo021999]

Cheer!