Results 1 to 1 of 1
  1. #1
    Join Date
    Jun 2011
    Posts
    2

    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.



    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!
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •