VBA Comparing two arrays in different spreadsheets

[montecarlo2012]

Hello. I have a little question about how to compare two arrays in different spreadsheets, AND highlight the matches. To make it short I just paste a small example.
[TABLE="width: 326"]
[TR]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD] [/TD]
[TD]morrow[/TD]
[TD]rob[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD] [/TD]
[TD]krumholtz[/TD]
[TD]david[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD] [/TD]
[TD]hirsch[/TD]
[TD]judd[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD] [/TD]
[TD]ballard[/TD]
[TD]alimi[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD] [/TD]
[TD]lloyd[/TD]
[TD]sabrina[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD] [/TD]
[TD]sumika[/TD]
[TD]aya[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD] [/TD]
[TD]macnicol[/TD]
[TD]peter[/TD]
[TD="align: right"]19[/TD]
[/TR]
[/TABLE]

sheet 1
[TABLE="width: 320"]
[TR]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl66, width: 64"]A[/TD]
[TD="class: xl66, width: 64"]B[/TD]
[TD="class: xl66, width: 64"]C[/TD]
[TD="class: xl66, width: 64"]D[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl66"]bruno[/TD]
[TD="class: xl66"]dylan[/TD]
[TD="class: xl65, align: right"]100[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl66"]farr[/TD]
[TD="class: xl66"]diane[/TD]
[TD="class: xl65, align: right"]31[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl66"]rawat[/TD]
[TD="class: xl66"]nvai[/TD]
[TD="class: xl65, align: right"]64[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]4[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl66"]brown[/TD]
[TD="class: xl66"]sophina[/TD]
[TD="class: xl65, align: right"]967[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl66"]sumika[/TD]
[TD="class: xl66"]aya[/TD]
[TD="class: xl65, align: right"]15[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]6[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl66"]macnicol[/TD]
[TD="class: xl66"]peter[/TD]
[TD="class: xl65, align: right"]19[/TD]
[/TR]
[/TABLE]

sheet 2
Thank you for reading this.

 

[ravishankar]

montecarlo simulation is very famous for its whatif analysis.
run the following macro codes

Sub montecarlo()
Dim z As String, x As Integer, y As Integer, a As Integer, b As Integer
y = Cells(1, Columns.Count).End(xlToLeft).Column
x = Cells(Rows.Count, 1).End(xlUp).Row
For a = 2 To x
For b = 1 To y
If Sheets("sheet1").Cells(a, b) = Sheets("sheet2").Cells(a, b) Then
Sheets("sheet1").Cells(a, b).Font.ColorIndex = 3
Sheets("sheet1").Cells(a, b).Font.Bold = True
End If
Next b
Next a
MsgBox "complete"
End Sub

if 2 cells are identical, it will highlight it as bold and red font
Ravishankar

 

[MickG]

Try this for data in sheets1 & sheet2.

Sub MG27Oct00
Dim Rng1 As Range, Rng2 As Range, Dn As Range, n As Long
Dim Ac As Long, Ray As Variant, Q As Variant, K As Variant
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare


Set Rng1 = Sheets("Sheet1").Cells(1).CurrentRegion
Set Rng2 = Sheets("Sheet2").Cells(1).CurrentRegion
Ray = Array(Rng1, Rng2)


For Ac = 0 To 1
    For Each Dn In Ray(Ac)
        If Not .Exists(Dn.Value) Then
            ReDim nRay(1 To 2)
            Set nRay(Ac + 1) = Dn
            .Add Dn.Value, nRay
        Else
            Q = .Item(Dn.Value)
             Set Q(Ac + 1) = Dn
           .Item(Dn.Value) = Q
        End If
    Next Dn
Next Ac

For Each K In .keys
    If .Item(K)(1) <> "" And .Item(K)(2) <> "" Then
        .Item(K)(1).Font.Color = vbRed
        .Item(K)(2).Font.Color = vbRed
    End If
Next K
End With
End Sub

Regards Mick

 

[montecarlo2012]

Thank you MickG. I like it. worked perfect

 

[MickG]

You're welcome