Jai another excellent solution provided by Rajan Berma
Try this : Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo Err: Dim lngValue As Long Dim intICounter As Integer Dim strMsg As String Dim lngValue2 As Long lngValue = Target.Value lngValue2 = Target.Offset(0, -1).Value If Intersect(Target, Range("C:C")) Then For intICounter = 1 To Target.Row - 1 If Range("B" & intICounter).Value = lngValue2 And Range("C" & intICounter).Value = lngValue Then strMsg = strMsg & "Row" & intICounter & vbCrLf Next intICounter End If If strMsg <> "" Then strMsg = "Duplicate Record of " & Target.Value & vbCrLf & vbCrLf & strMsg MsgBox strMsg End If Err: If Err.Number <> 0 Then Exit Sub End If End Sub 2011/10/4 ChilExcel <chilexcel...@gmail.com> > Please see Mathai solution proposed by Sam Chacko > Excellent! > > > Private Sub Worksheet_Change(ByVal Target As Range) > Dim lngCount As Long > Dim lngLoop As Long > Dim strMsg As String > lngCount = > Evaluate("=SUMPRODUCT((Hoja1!$B$2:$B$57&Hoja1!$C$2:$C$57<>"""")*(Hoja1!$B$2:$B$57=Hoja1!B" > & Target.Cells(1).Row & ")*(Hoja1!$C$2:$C$57=Hoja1!C" & Target.Cells(1).Row > & "))") > If lngCount > 1 Then > strMsg = "Pair duplicated " & lngCount & " times in " & > Application.Rept(vbNewLine, 2) > For lngCount = lngCount To 1 Step -1 > strMsg = strMsg & "Row " & > Evaluate("=SUMPRODUCT(LARGE((ROW(Hoja1!$B$2:$B$57))*(Hoja1!$B$2:$B$57&Hoja1!$C$2:$C$57<>"""")*(Hoja1!$B$2:$B$57=Hoja1!B" > & Target.Cells(1).Row & ")*(Hoja1!$C$2:$C$57=Hoja1!C" & Target.Cells(1).Row > & ")," & lngCount & "))") & vbNewLine > Next lngCount > MsgBox strMsg, vbOKOnly + vbInformation, "Duplicates" > End If > > End Sub > > Chilexcel > 2011/10/4 Jai <jaihumtu...@gmail.com> > >> I need also this VB Code >> >> >> >> >> >> On Mon, Oct 3, 2011 at 4:41 AM, ChilExcel <chilexcel...@gmail.com> wrote: >> >>> Urgent !! >>> i Need VB lines codes for >>> >>> 2011/10/1 ChilExcel <chilexcel...@gmail.com> >>> >>>> hi >>>> >>>> i Need VB lines codes for >>>> Alert MsgBox Pair duplicate entry in row >>>> >>>> Column D function account and duplicates alerts (NO Problem!) >>>> Attach File , thank you all >>>> >>>> -- >>>> Visita ; http://sites.google.com/site/chilexcel/Home >>>> Visita ; http://www.youtube.com/user/timextag41 >>>> >>>> -- >>>> >>>> ---------------------------------------------------------------------------------- >>>> Some important links for excel users: >>>> 1. Follow us on TWITTER for tips tricks and links : >>>> http://twitter.com/exceldailytip >>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com >>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >>>> >>>> To post to this group, send email to excel-macros@googlegroups.com >>>> >>>> <><><><><><><><><><><><><><><><><><><><><><> >>>> Like our page on facebook , Just follow below link >>>> http://www.facebook.com/discussexcel >>>> >>> >>> >>> >>> -- >>> Visita ; http://sites.google.com/site/chilexcel/Home >>> Visita ; http://www.youtube.com/user/timextag41 >>> >>> -- >>> >>> ---------------------------------------------------------------------------------- >>> Some important links for excel users: >>> 1. Follow us on TWITTER for tips tricks and links : >>> http://twitter.com/exceldailytip >>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >>> 3. Excel tutorials at http://www.excel-macros.blogspot.com >>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >>> >>> To post to this group, send email to excel-macros@googlegroups.com >>> >>> <><><><><><><><><><><><><><><><><><><><><><> >>> Like our page on facebook , Just follow below link >>> http://www.facebook.com/discussexcel >>> >> >> -- >> >> ---------------------------------------------------------------------------------- >> Some important links for excel users: >> 1. Follow us on TWITTER for tips tricks and links : >> http://twitter.com/exceldailytip >> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >> 3. Excel tutorials at http://www.excel-macros.blogspot.com >> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >> >> To post to this group, send email to excel-macros@googlegroups.com >> >> <><><><><><><><><><><><><><><><><><><><><><> >> Like our page on facebook , Just follow below link >> http://www.facebook.com/discussexcel >> > > > > -- > Visita ; http://sites.google.com/site/chilexcel/Home > Visita ; http://www.youtube.com/user/timextag41 > > -- Visita ; http://sites.google.com/site/chilexcel/Home Visita ; http://www.youtube.com/user/timextag41 -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel