A Simple Email Validation Question

[cfaust]

I have a column full of email addresses and I have been trying to create a macro that simply makes sure each address at least has an @ and . in it to eliminate the number of emails that bounce back.

I would prefer the macro to either check the range I highlighted or even the specific column since it will always be column A.

Also it would be ideal if it simply just highlighted the invalid emails. I was hoping this would be an easy fix for macro gurus.

After getting on the Internet and trying to learn VB and macros, below is what I have but its not working and it is using a message box instead of highlighting since I am not sure how to do that.

Sub ValidEmail()
Dim Cel As Range
For Each Cel In Intersect(Selection, ActiveSheet.UsedRange)

  If InStr(1, Cel.Value, ".") = 0 Or InStr(1, Cel.Value, "@") = 0 Then
           MsgBox "Email address not valid"
           bNotValid = True
        End If
        Cel.Value = Cel.Value & ";"
Next
End Sub

Thanks for any help and tips.

[cfaust]

Sorry about that.

[Leith Ross]

Hello cfaust,

Welcome to the Forum!

This macro will highlight any invalid email addresses in column "A" start with cell "A2". I am assuming you have a header in in "A1".

Sub ValidEmail()

  Dim Cel As Range
  Dim Rng As Range
  Dim RngEnd As Range

    Set Rng = Range("A2")
    Set RngEnd = Cells(Rows.Count, Rng.Column).End(xlUp)
    If RngEnd.Row < Rng.Row Then Exit Sub Else Set Rng = Range(Rng, RngEnd)

    For Each Cel In Rng

      If Not (Cel Like Like "[!.]*?[!.]@[!-]?*?.?*[!-]") Then
         Cel.Interior.ColorIndex = 6
      End If
 
    Next Cel

End Sub

[cfaust]

Hello cfaust,

Welcome to the Forum!

This macro will highlight any invalid email addresses in column "A" start with cell "A2". I am assuming you have a header in in "A1".

Sub ValidEmail()

  Dim Cel As Range
  Dim Rng As Range
  Dim RngEnd As Range

    Set Rng = Range("A2")
    Set RngEnd = Cells(Rows.Count, Rng.Column).End(xlUp)
    If RngEnd.Row < Rng.Row Then Exit Sub Else Set Rng = Range(Rng, RngEnd)

    For Each Cel In Rng

      If Not (Cel Like Like "[!.]*?[!.]@[!-]?*?.?*[!-]") Then
         Cel.Interior.ColorIndex = 6
      End If
 
    Next Cel

End Sub

Thanks a lot for the help and the above code you are right about having a header so starting at A2 is perfect.

When I run the macro I get a syntax error on the line.

If Not (Cel Like Like "[!.]*?[!.]@[!-]?*?.?*[!-]") Then

I am trying to figure out why but I thought I would post it while I attempt to debug.

[Leith Ross]

Hello cfaust,

If only I could type. Remove one of the "Like" statements in the line.

    If Not (Cel Like "[!.]*?[!.]@[!-]?*?.?*[!-]") Then

[cfaust]

That was it thanks a lot for your help it works perfectly.

[arthurbr]

Mod bump,,,,,

[arthurbr]

If you only want to highlight,you could use Conditional Formatting also.
Use

=NOT(ISNUMBER(FIND("@",A1)))

as formula for the CF