Excel 2000-VBA-exact match 3 columns in 1 row to rest of sheet.

  • Thread starter Thread starter lunker55
  • Start date Start date
L

lunker55

I have 500 rows with 10 columns.

It is based on columns 1,2,3
Basically, I need to flag duplicates based on the first three columns.

ie:
A1 B1 C1
Orange Blue 22

If I enter the same combination in any other row, I want a message box to
pop up giving me an option to accept.
Does this make sense??

Joe
 
Joe,

You can use the worksheet change event. Copy the code below, right click on
the sheet tab, select "View Code" and paste the code into the window that
appears.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:C")) Is Nothing Then Exit Sub
For Each myCell In Intersect(Range("A:A"), Target.CurrentRegion)
If myCell.Row <> Target.Row Then
If Cells(Target.Row, 1).Value = myCell(1, 1).Value Then
If Cells(Target.Row, 2).Value = myCell(1, 2).Value Then
If Cells(Target.Row, 3).Value = myCell(1, 3).Value Then
MsgBox "Hey, that's the same as in row " & myCell.Row & "!"
Exit Sub
End If
End If
End If
End If
Next myCell

End Sub
 
Joe,

You want it flagged as you try to enter the information. Try this. Select
columns A:C. The active (white) cell should be A1. Data - Validation -
Custom:

=SUMPRODUCT(($A$1:$A$65535=$A1)*($B$1:$B$65535=$B1)*($C$1:$C$65535=$C1))=1

You can customize Data Validation to give nice messages when the user messes
up.
 
Back
Top