I’m trying to initiate a worksheet change event which will find duplicate values across multiple worksheets.
So if I enter a number in Sheet1 which is already in Sheet2, I’ll get a message informing me and then delete the number which I just entered.
(and vise versa if I enter a number of Sheet2, it will check Sheet1 for duplicate)
I’ve made the below, but the problem is it searches from left-top to right-bottom so if I enter a number further down the list, it deletes the existing number – not the new one.
I just need something to delete the new number.
The numbers are in A2:N36 of both sheets.
On each sheet I have a Worksheet_SelectionChange to call a separate macro “DuplicateValue”
This is the macro to check for duplicates.
So if I enter a number in Sheet1 which is already in Sheet2, I’ll get a message informing me and then delete the number which I just entered.
(and vise versa if I enter a number of Sheet2, it will check Sheet1 for duplicate)
I’ve made the below, but the problem is it searches from left-top to right-bottom so if I enter a number further down the list, it deletes the existing number – not the new one.
I just need something to delete the new number.
The numbers are in A2:N36 of both sheets.
On each sheet I have a Worksheet_SelectionChange to call a separate macro “DuplicateValue”
This is the macro to check for duplicates.
Code:
Sub DuplicateValue()
Dim Values, Values2 As Range, Cell
'set worksheets and ranges to cover where the numbers are entered
Set Values = Worksheets("Sheet1").Range("A1:N36")
Set Values2 = Worksheets("Sheet2").Range("A1:N36")
'check for duplicates on first worksheet
For Each Cell In Values
If WorksheetFunction.CountIf(Values, Cell.Value) > 1 Then
MsgBox Cell & " is already appearing!!"
Cell.ClearContents
End If
Next Cell
'check for duplicates on second worksheet
For Each Cell In Values2
If WorksheetFunction.CountIf(Values2, Cell.Value) > 1 Then
MsgBox Cell & " is already appearing!!"
Cell.ClearContents
End If
Next Cell
End Sub