Unanswered: VBA Match Highlight to Compare Data on Two Spreadsheets
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.
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",
If origRng Is Nothing Then Exit Sub
Set compRng = Application.InputBox("Choose the second range", "Range 2",
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
If bMatch = True Then
rng1.Interior.ColorIndex = 4
Dim FoundCell As Range
Dim LastCell As Range
Dim FirstAddr As String
Set LastCell = .Cells(.Cells.Count)
Set FoundCell = Sheet2.Range("A1:A620").Find(What:="Sheet!1.A1:BF
114", after:=LastCell, LookIn:=xlValues, LookAt:=xlPart,
If Not FoundCell Is Nothing Then
FirstAddr = FoundCell.Address
Do Until FoundCell Is Nothing
Set FoundCell = Sheet2.Range("A1:A620").FindNext
If FoundCell.Address = FirstAddr Then
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!