Hi,

I should have trapped for empty or multiple cells so try this instead

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A:A")) Is Nothing Then
With CreateObject("VBScript.RegExp")
.Pattern = "^[\w-\.]+@([\w-]+\.)+[A-Za-z]{2,3}$"
If Not .test(Target.Value) Then
MsgBox Target.Value & " is not a valid Email"
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End If
End With
End If
End Sub

Mike
"Mike H" wrote:

Hi,

Here's one way and this works on column A so change to suit. Right click
your sheet tab, view code and paste this in. Try entering valid and invalid
emaill addresses in column A

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
With CreateObject("VBScript.RegExp")
.Pattern = "^[\w-\.]+@([\w-]+\.)+[A-Za-z]{2,3}$"
If Not .test(Target.Value) Then
MsgBox Target.Value & " is not a valid Email"
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End If
End With
End If
End Sub

Mike

"Lakshmanagm" wrote:

in excel sheet one of the column is Email. i wnat to put
conditions/validations/functions to that email cell for it does not allowing
the wrong email format id's --like only text or only numericals or etc..(
wrong email format).
i.e if typed or copied the invalid email format id's i want to put alert
massage . Please tell me how it is possible.