• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Duplicates values with different colours

N

Nabeel

Member
hello all

PFA i want different color of repeated values or names in given sheet

Nabeel
 

Attachments

  • 2323.xlsx
    41.2 KB · Views: 10
One way
Code: 
Sub test()
    Dim r As Range, dic As Object, w
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = 1
    With Range("c2", Range("c" & Rows.Count).End(xlUp))
        .Interior.ColorIndex = xlNone
        For Each r In .Cells
            If Not dic.exists(r.Value) Then
                ReDim w(1 To 2)
                Set w(1) = r
                With Application.WorksheetFunction
                    w(2) = Array(.RandBetween(0, 255), .RandBetween(0, 255), .RandBetween(0, 255))
                End With
                dic(r.Value) = w
            Else
                w = dic(r.Value)
                r.Interior.Color = RGB(w(2)(0), w(2)(1), w(2)(2))
                If Not IsEmpty(dic(r.Value)(1)) Then dic(r.Value)(1).Interior.Color = RGB(w(2)(0), w(2)(1), w(2)(2))
                w(1) = Empty: dic(r.Value) = w
            End If
        Next
    End With
End Sub
 
I don't understand what you are asking.

Upload a workbook with before/after clearly showing what you are trying to do.
 
You must log in or register to reply here.
Back
Top