Comparing Dynamically Assigned Variables In An Array

[opaquefruitcake]

Hello,

I am writing a program that combines and compares the values in columns 1 and 2 using an array, then changes the color of the row those values appear it if it show up more than once in the spreadsheet. I know there is an easier way to do this with formulas, but the person I am making this for wants it bound to a button, and I figured this was a good opportunity to learn more about arrays. I currently have the loop to assign the values, but am having trouble finding a way to compare those values to each other. Here is what I have so far:

Private Sub strtButton_Click()
    
    Dim lastRow As Long
    Dim i As Long
    Dim fullValue() As String
    Dim j As Long
    
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    ReDim fullValue(1 To lastRow)
    'Determines how many items are in sheet and makes that the length of array fullValue
    
    For i = 1 To lastRow
        
            fullValue(i) = Worksheets("Sheet1").Range("A" & i) & Worksheets("Sheet1").Range("B" & i)
                'Assigns each row a value in array fullValue
       
                        
            Range("C" & i) = fullValue(i) 'Used as a test to make sure above formula worked.
     
     Next i        
    
End Sub

I have attempted to do several If statements to compare the variables to determine if the color of the row should be changed, but I could not figure out a way to test every value in the array against every other one. I also attempted to use a parallel array to test each value, but couldn't figure that out either. Were any of those possible solutions close? Also is there a more efficient way in VBA to do this other than an array? Thanks in advance for any assistance anyone can offer.

 

[pgc01]

Hi

If I understood correctly, this is another option for the code you posted:

Private Sub strtButton_Click()
    Dim lastRow As Long
    
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    With Range("C1:C" & lastRow)
        .Formula = "A1&B1"
        .Value = .Value
    End With
End Sub

As for highlighting the duplicate cells, the easiest way would be to use conditional formatting.
Is that OK?

 

[Fluff]

How about

Sub opaquefruitcake()
   Dim Ary As Variant
   Dim v As String
   Dim i As Long
   Dim Ws As Worksheet
   
   Set Ws = Sheets("Test")
   Ary = Ws.Range("A1", Ws.Range("A" & Rows.Count).End(xlUp).Offset(, 1)).Value2
   
   With CreateObject("Scripting.dictionary")
      For i = 1 To UBound(Ary)
         v = Ary(i, 1) & "|" & Ary(i, 2)
         If Not .Exists(v) Then
            .Add (v), i
         Else
            Ws.Range("A" & i).Resize(, 2).Interior.Color = 45678
            Ws.Range("A" & .Item(v)).Resize(, 2).Interior.Color = 45678
         End If
      Next i
   End With
End Sub