excelvba

Excel VBA Highlight Cells Based on Same Groups from Different Column


I have a large data set in which Column A specifies a Group name and column D specifies a number that needs to be unique PER group.

I'd like to use VBA to highlight any Duplicate cells in Column D, for same Groups from Column A.

Ex:

In this case, cells for Group 1 containing "20" would be highlighted and cells for Group 2 containing "33" would be highlighted.

A B C D
Group 1 20
Group 1 15
Group 1 12
Group 1 16
Group 1 2
Group 1 20
Group 2 1
Group 2 33
Group 2 18
Group 2 15
Group 2 4
Group 2 33

Solution

  • Modifying my linked post:

    Sub HiliteDups()
        Const HILITE_COLOR = vbRed
        Dim ws As Worksheet, k
        Dim dict As Object, rng As Range, data, r As Long
         
        'Set dict = CreateObject("scripting.dictionary") 'Windows
        Set dict = New Dictionary                        'Cross-platform
        '   (https://github.com/VBA-tools/VBA-Dictionary)
        
        Set ws = ActiveSheet 'for example
        Set rng = ws.Range("A2:D" & ws.Cells(Rows.Count, "A").End(xlUp).Row)
        rng.Columns(4).Interior.ColorIndex = xlNone 'clear flags
        data = rng.Value  'get data as array (for faster looping)
        
        For r = 1 To UBound(data, 1)           'loop over range data
            k = data(r, 1) & "<>" & data(r, 4) 'composite key A+D
            If dict.Exists(k) Then
                If dict(k) > 0 Then 'need to color the first row for this key?
                    rng.Cells(dict(k), 4).Interior.Color = HILITE_COLOR
                    dict(k) = 0
                End If
                rng.Cells(r, 4).Interior.Color = HILITE_COLOR 'color this row
            Else
                dict.Add k, r 'remember this row in case there are duplicates later
            End If
        Next r
    End Sub
    

    EDIT - allow for use of cross-platform dictionary class from:
    https://github.com/VBA-tools/VBA-Dictionary

    And another alternative: using a Collection with keys

    Sub HiliteDups()
        Const HILITE_COLOR = vbRed
        Dim ws As Worksheet, k As String, valA, valD
        Dim col As Collection, rng As Range, data, r As Long, v
         
        Set ws = ActiveSheet 'for example
        Set rng = ws.Range("A2:D" & ws.Cells(Rows.Count, "A").End(xlUp).Row)
        rng.Columns(4).Interior.ColorIndex = xlNone 'clear flags
        data = rng.Value  'get data as array (for faster looping)
        
        Set col = New Collection 'for tracking unique A+D combinations
        For r = 1 To UBound(data, 1)           'loop over range data
            valA = data(r, 1)
            valD = data(r, 4)
            If Len(valA)>0 And Len(valD)>0 Then
                k = valA & "<>" & valD 'composite key A+D
                v = KeyValue(col, k)
                If Not IsEmpty(v) Then
                    If v > 0 Then 'need to color the first row for this key?
                        rng.Cells(v, 4).Interior.Color = HILITE_COLOR
                        col.Remove k 'remove keyed value
                        col.Add 0, k 're-add as zero
                    End If
                    rng.Cells(r, 4).Interior.Color = HILITE_COLOR 'color this row
                Else
                    col.Add Item:=r, Key:=k 'remember this row and key in case there are duplicates later
                End If  'key was found?
            End If      'values in A and D?
        Next r
    End Sub
    
    'Retrieve value for key `k` from collection `col`
    '  Returns Empty if there's no such key
    Function KeyValue(col As Collection, k As String)
        On Error Resume Next 'ignore error if no match for `k`
        KeyValue = col.Item(k)
    End Function