Method union of object Global failed

[mumps]

I have the following macro which produces the error:

Sub Test()
    Dim uRng As Range
    Dim ws As Worksheet
    For Each ws In Sheets
        If ws.Name <> "Summary" Then
            If Not uRng Is Nothing Then
                Set uRng = Union(uRng, ws.Cells(6, 3))
            Else
                Set uRng = ws.Cells(6, 3)
            End If
        End If
    Next ws
End Sub

After doing much research, I can't seem to find a solution to the problem. Any suggestions would be greatly appreciated.

 

[RoryA]

You can't union a range across different worksheets.

 

[mumps]

Thank you for your quick response. I'm trying to find duplicate values in the same cell across multiple sheets and then highlight the duplicates, if any. For example, check the values in all the C6 cells in all the sheets and highlight the duplicates. Would you have any suggestions? Perhaps I should have posted this question originally. I can start a new thread if you feel that is appropriate.

 

[RoryA]

You could use a dictionary to store the values as keys and the first cell as the item. Then you test for the existence of each value in the loop. Something like this (untested):

Sub Test()
    Dim uRng As Range
    Dim ws As Worksheet
    Dim d As Object
    
    Set d = CreateObject("scripting.dictionary")
    For Each ws In Sheets
        If ws.Name <> "Summary" Then
            If d.exists(ws.Cells(6, 3).Value) Then
                d(ws.Cells(6, 3).Value).Interior.Color = vbRed
                ws.Cells(6, 3).Interior.Color = vbRed
            Else
                Set d(ws.Cells(6, 3).Value) = ws.Cells(6, 3)
            End If
        End If
    Next ws
End Sub

 

[mumps]

Thank you so very much. I will give that a try.

 

[mumps]

Worked like a charm. Thanks again.

 

[RoryA]

Glad to help.