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.

	A	B	C	D
1		morrow	rob	60
2		krumholtz	david	20
3		hirsch	judd	10
4		ballard	alimi	5
5		lloyd	sabrina	80
6		sumika	aya	15
7		macnicol	peter	19

sheet 1

	A	B	C	D
1		bruno	dylan	100
2		farr	diane	31
3		rawat	nvai	64
4		brown	sophina	967
5		sumika	aya	15
6		macnicol	peter	19

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