Need help with Range in VBA code.

[shba]

Hi the code below is a rule which highlights duplicates in different colors, can anyone help me with the Set Rng bit, I need it to simply work on the entire document not just column A.

Also the code in italics cannot be changed I need it to work on the entire document AS LONG AS it is on columns I choose.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim Rng As Range, Dn As Range, rngFound As Range
    Dim K
    Dim col
    Dim c As Integer
    
    Set rngFound = Target.Parent.Rows(1).Find("IP", , xlValues, xlWhole)
    If rngFound Is Nothing Then Exit Sub
    If Target.Column = rngFound.Column Then
        Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
        With CreateObject("scripting.dictionary")
            .CompareMode = vbTextCompare
            For Each Dn In Rng
                If Not .Exists(Dn.Value) Then
                    .Add Dn.Value, Dn
                Else
                    Set .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
                End If
            Next
            col = Array(3, 50, 6, 7, 8, 34, 35, 38, 39, 50, 45, 46)
            For Each K In .keys
                If .Item(K).Count > 1 Then
                    c = IIf(c = 12, 0, c)
                    .Item(K).Interior.ColorIndex = col(c)
                    c = c + 1
                End If
            Next K
        End With
    End If
    
End Sub

Thank you

 

[Firefly2012]

Hi

Do you need the Rng to be the same column as the changed value (ie Target)? If so then maybe:

'Replace this:  Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))

'With:

Set Rng = Application.Intersect(Target.EntireColumn,Me.UsedRange)

 

[shba]

Hi

Do you need the Rng to be the same column as the changed value (ie Target)? If so then maybe:

'Replace this:  Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))

'With:

Set Rng = Application.Intersect(Target.EntireColumn,Me.UsedRange)

Doesn't work, here is a test file
https://www.dropbox.com/s/fov76o2lm56y8h3/test.xlsx

I need to find a way for the code to work on each column and not just the first and to be able to name which columns it works so if looking at the example to tell it just work on IP and Numbers and not on Payout Method.


Thanks