Comparing Two Lists

  • Thread starter Thread starter MTBer
  • Start date Start date
M

MTBer

I have to compare two account balance downloads to reconcile an inte
company account. The only field I can match between the two lists i
the value, unfortunately no dates correspond, nor text fields.

I had originally planned on using a vba routine that coloured value
found in both lists, or an array formula. i went with the vba
Unfortunately the code identified all the matching cells, ie, the valu
£300 appeared twice in List A but only once in List B. The cod
coloured both of these values in List A, and the singular entry in Lis
B.

I only need to colour one value in List A as it nets off One value i
List B.

The code being used is;
----------------------------------
Sub CompareLists()
Call Compare1
Call Compare2

Range("A1").Select
MsgBox "Completed comparison"
End Sub

Sub Compare1()


'set orginal range to compare
Range("A5").Select
Set OrgRg = Range(ActiveCell, ActiveCell.End(xlDown))
'Reset Colors
OrgRg.Interior.ColorIndex = xlNone
OrgRg.Font.ColorIndex = 0

'set other data to compare range
Range("B5").Activate
Set ToCompRg = Range(ActiveCell, ActiveCell.End(xlDown))

Application.ScreenUpdating = False

'Compare NOW
For Each oCell In OrgRg
For Each cCell In ToCompRg
If oCell = cCell Then
'MsgBox oCell.Text & ":" & cCell
With oCell.Interior
.ColorIndex = 15 'Grey
.Pattern = xlSolid
End With
oCell.Font.ColorIndex = 5 'Blue
End If
Next cCell
Next oCell
'Application.ScreenUpdating = True
'MsgBox "Completed comparison"

End Sub

Sub Compare2()


'set orginal range to compare
Range("B5").Select
Set OrgRg = Range(ActiveCell, ActiveCell.End(xlDown))
'Reset Colors
OrgRg.Interior.ColorIndex = xlNone
OrgRg.Font.ColorIndex = 0

'set other data to compare range
Range("A5").Activate
Set ToCompRg = Range(ActiveCell, ActiveCell.End(xlDown))

Application.ScreenUpdating = False

'Compare NOW
For Each oCell In OrgRg
For Each cCell In ToCompRg
If oCell = cCell Then
'MsgBox oCell.Text & ":" & cCell
With oCell.Interior
.ColorIndex = 15 'Grey
.Pattern = xlSolid
End With
oCell.Font.ColorIndex = 5 'Blue
End If
Next cCell
Next oCell

Application.ScreenUpdating = True
'MsgBox "Completed comparison"

End Sub

--------------------------

Can this been one with an array formula, or conditional formatting??

Thank you for your assistance,
Paul
:confused
 
Back
Top