I am new to VBA and need some help.

I have found two VBA macros one that matches and highlights data on two different spreadsheets, and one that is supposed to to make sure the data that is being matched does not keep matching to the same data over and over.

I am trying to reconcile some bank deposits from a spreadsheet from our bank account with a spreadsheet that is created by workers in the office to make sure all of the deposits were posted. The problem I am running into is, the Bank spreadsheet will have 6 $20.00 amounts listed, the macro I'm using will highlight all six as being found when there are only 2 $20.00 amounts listed on the workers spreadsheet. That is the only bug, otherwise the macro works great. I found a FindNext Macro that should solve my problem with the $20.00 being marked as found 6 times, at least I think it will, but I do not know how to integrate the two to get it to work right. Here is the code.



Sub FindDuplicates()
Dim rng1 As Range
Dim rng2 As Range
Dim bMatch As Boolean
Dim origRng As Range
Dim compRng As Range

On Error Resume Next
Set origRng = Application.InputBox("Choose the first range", "Range 1",
Type:=8)
If origRng Is Nothing Then Exit Sub
Set compRng = Application.InputBox("Choose the second range", "Range 2",
Type:=8)

For Each rng1 In origRng
bMatch = False
For Each rng2 In compRng
If rng1 = rng2 Then
bMatch = True
rng2.Interior.ColorIndex = 4
ElseIf bMatch = False Then
rng1.Interior.ColorIndex = 3
End If
Next rng2
If bMatch = True Then
rng1.Interior.ColorIndex = 4
End If
Next rng1
End Sub
Sub FindDuplicate2()

Dim FoundCell As Range
Dim LastCell As Range
Dim FirstAddr As String

With Sheet2.Range("A1:A620")
Set LastCell = .Cells(.Cells.Count)
End With
Set FoundCell = Sheet2.Range("A1:A620").Find(What:="Sheet!1.A1:BF
114", after:=LastCell, LookIn:=xlValues, LookAt:=xlPart,
SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False)

If Not FoundCell Is Nothing Then
FirstAddr = FoundCell.Address
End If
Do Until FoundCell Is Nothing
Debug.Print FoundCell.Address
Set FoundCell = Sheet2.Range("A1:A620").FindNext
(after:=FoundCell)
If FoundCell.Address = FirstAddr Then
Exit Do
End If
Loop
End Sub



I need to match cells A1:A620 from Sheet 2 to the range of cells A2:BG114 on Sheet 1. It would also be nice if I could have the dollar amounts not matched on Sheet 1 highlighted in red. I was able to get it to highlight all non matches, but it highlighted the blank cells red too. I don't need the null cells highlighted.

I have been trying to figure this out, so if something doesn't look right that is why. I have attached the spreadsheet for reference. Any help would be greatly appreciated!