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.
Highlight Duplicates Across Sheets As New Info Added
-
BSC -
April 30, 2018 at 9:48 PM -
Thread is marked as Resolved.
-
-
-
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
C.EntireRow.Delete
Else
'when information is found
MsgBox "Invoice already paid!"
End If
End If
Next C
End Sub
[/VBA] -
That is perfect!!!! Thank you so much!
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!