Highlight Duplicates Across Sheets As New Info Added

  • I apologize in advance - this is my first question. I have a workbook with two sheets. On the first sheet, "Account", we enter invoices to be paid. The workbook has a simple macro where when I put a "p" in Column K on the Account sheet, it shoots that line entry to the second sheet "Paid". So, I put the invoice on the first sheet to be paid and then once it is paid I enter a "p" and it goes to the second sheet. I have conditional formatting on the "Invoice No." column of the first sheet to check for duplicate invoice numbers, but it only checks for duplicates on that sheet. What I'm trying to figure out how to do is, when I enter an invoice on the first sheet I'd like to check and make sure that it hasn't already been paid - I want to know whether the invoice number on the Account sheet has a duplicate on the Paid sheet.

  • Here is some code that might work:

    [VBA]Private Sub Worksheet_Change(ByVal Target As Range)
    Dim C As Range, cellFound As Range, cellSearch As Range
    If Intersect(Target, Me.Range("K:K")) Is Nothing Then Exit Sub
    For Each C In Intersect(Target, Me.Range("K:K")).Cells
    If C.Text = "p" Then
    Set cellSearch = Intersect(Target.EntireRow, Me.Range("I:I"))
    Set cellFound = Worksheets("Paid").Cells(Rows.Count, "I").EntireColumn.Find(cellSearch.Value)
    If cellFound Is Nothing Then
    C.EntireRow.Copy Worksheets("Paid").Cells(Rows.Count, "K").End(xlUp).Offset(1).EntireRow
    'when information is found
    MsgBox "Invoice already paid!"
    End If
    End If
    Next C
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!