duplicate warning code for 3 columns

[maksinx]

hello everyone,
i have a below codes which works fine individually and i would like add the code 2 into the code1 which both stated below but i have tried to put in a module and also tried to put at the bottom of the code1 and gave me a warning it seems i have done something wrong.What is the way to make two codes work in one worksheet.?

in brief i want to combine to codes into a one worksheet.
is it also possible to set up duplicate warning for two columns instead of two which is shown below.

thanks in advance

please advice


CODE1
[VBA]
Private Sub Backup_Click()
ActiveWorkbook.SendMail xxx@email.com
End Sub
Private Sub CommandButton1_Click()
Calculate_Eta_Report_For_Next_Five_Days
End Sub

Private Sub CommandButton2_Click()
Call Delete_Report
End Sub
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "J:J" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Value = UCase(.Value)
If .Value = "EXSA02" Or .Value = "EXSA03" Then
If .Offset(0, 3).Value = "" Then
MsgBox "EXSA SIP NUMBER CANNOT BE BLANK, PLS ENTER EXSA SIP NUMBER BEFORE CUSTOMER NAME"
.Value = ""
End If
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
[/VBA]
CODE2
[VBA]
Private Sub Worksheet_Change(ByVal Target As Range)
Dim IsDuplicate As Integer
Dim IsDuplicate1 As Integer
Dim IsDuplicate2 As Integer
Set WF = WorksheetFunction
IsDuplicate1 = WF.CountIf(Columns("B:B"), Target.Value)
IsDuplicate2 = WF.CountIf(Columns("F:F"), Target.Value)
IsDuplicate = IsDuplicate1 + IsDuplicate2
If IsDuplicate > 1 Then MsgBox "DUPLICATE!", vbCritical: Application.Undo
End Sub
[/VBA]

[Bob Phillips]

Why not just?

[vba]
Option Explicit

Private Sub Backup_Click()
ActiveWorkbook.SendMail xxx@email.com
End Sub

Private Sub CommandButton1_Click()
Calculate_Eta_Report_For_Next_Five_Days
End Sub

Private Sub CommandButton2_Click()
Call Delete_Report
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "J:J"
Dim IsDuplicate As Long
Dim IsDuplicate1 As Long
Dim IsDuplicate2 As Long

On Error Goto ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Value = UCase(.Value)
If .Value = "EXSA02" Or .Value = "EXSA03" Then
If .Offset(0, 3).Value = "" Then
MsgBox "EXSA SIP NUMBER CANNOT BE BLANK, PLS ENTER EXSA SIP NUMBER BEFORE CUSTOMER NAME"
.Value = ""
Exit Sub
End If
End If
End With
End If

Set WF = WorksheetFunction
IsDuplicate1 = WF.CountIf(Columns("B:B"), Target.Value)
IsDuplicate2 = WF.CountIf(Columns("F:F"), Target.Value)
IsDuplicate = IsDuplicate1 + IsDuplicate2
If IsDuplicate > 1 Then MsgBox "DUPLICATE!", vbCritical: Application.Undo

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

[maksinx]

hello xld,
is that above code covers for 3 columns?
looks like only for two column.
please advice

[maksinx]

Dear xld,
i have tried the code you provided but it gives the below warning.
"compile error in hidden module sheet2"

please advice

[Bob Phillips]

Can you post the book?

[maksinx]

i have attached the wrkbook as requested,
thanks for helping me out.

i have also highligted columns that i want to be warned for duplicate entries.

can you also check the code in module2 which usually worked fine,after doing a few modification on the file it doesnt wrok any more.

[maksinx]

dear xld
did you have time to check the file??

regards

[maksinx]

xld,
i have made below amendments and works fine,
thanks for your time.

regards



Private Sub Worksheet_Change(ByVal Target As Range)
Dim IsDuplicate As Integer
Dim IsDuplicate1 As Integer
Dim IsDuplicate2 As Integer
Dim IsDuplicate3 As Integer
Set WF = WorksheetFunction
IsDuplicate1 = WF.CountIf(Columns("i:i"), Target.Value)
IsDuplicate2 = WF.CountIf(Columns("n:n"), Target.Value)
IsDuplicate3 = WF.CountIf(Columns("o:o"), Target.Value)
IsDuplicate = IsDuplicate1 + IsDuplicate2 + IsDuplicate3
If IsDuplicate > 1 Then MsgBox "DUPLICATE!", vbCritical: Application.Undo
Const WS_RANGE As String = "J:J" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Value = UCase(.Value)
If .Value = "EXSA02" Or .Value = "EXSA03" Then
If .Offset(0, 3).Value = "" Then
MsgBox "EXSA SIP NUMBER CANNOT BE BLANK, PLS ENTER EXSA SIP NUMBER BEFORE CUSTOMER NAME"
.Value = ""
End If
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub