Highlight Cell that contains invalid email format

[Gareth_Evans (InterCall EMEA)]

Hi again, I've been working on a spreadsheet that will highlight cells
different colours based on criteria. I've been doing this with VBA...well,
trying I've got very very basic skills with VBA and have got stuck trying
to highlight a cell with an invalid email address.

I'd like to be able to check an email address is larger than 7 characters,
contains basic characters only and is in one of these formats:

(e-mail address removed)
(e-mail address removed)
(e-mail address removed)

(e-mail address removed)
(e-mail address removed)
(e-mail address removed)

(e-mail address removed)
(e-mail address removed)
(e-mail address removed)

Where the dots could also be a hyphen.

I've got this code together so far with the count and the character check
working but the email format check (ie: (e-mail address removed) isn't):

Sub IsEmail()
Dim RangeToCheck As Range, c As Range

Set RangeToCheck = Range("L1:L5000")
For Each c In RangeToCheck

If Len(c.Text) <= 7 Then
c.Interior.Color = vbRed

'This checks that the email is at least 7 characters long

ElseIf c.Text Like "*[!0-9a-z@._+-]*" Then
c.Interior.Color = vbRed

'This checks that the email doesn't contain any 'special' characters

ElseIf Not c.Text Like "*.*" Then
c.Interior.Color = vbRed

but this is as far as I get, I've tried various different things to try and
inderstand the format of the code like:

ElseIf Not c.Text Like "*@*" Then
c.Interior.Color = vbRed

ElseIf c.Text Like "*@*@*" Then
c.Interior.Color = vbRed

but it's getting me nowhere, I can't even see any definition in my results
to carry on. I bought myself a VBA book but I'm still getting to grips with
it. Feel free to comment telling me to read the whole book (several times, as
this is what it'll take I think) and I know I'm being a bit impatient, I can
see what I want to do in my head it's just getting it down that's driving me
mad!
Any help or comments would be welcomed.

Best regards,

Gareth

 

[Bob Phillips]

Sub IsEmail()
Dim RangeToCheck As Range, c As Range
Dim oRegEx As Object
Dim ValidEmail As Boolean

Set RangeToCheck = Range("L1:L5000")
For Each c In RangeToCheck

If Len(c.Text) <= 7 Then
c.Interior.Color = vbRed
Exit Sub
End If

Set oRegEx = CreateObject("VBScript.RegExp")
With oRegEx
.Pattern =
"^[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}$"
ValidEmail = .Test(c.Value)
End With
Set oRegEx = Nothing

If Not 7 Then c.Interior.Color = vbRed
Next c

End Sub


--

HTH

Bob

"Gareth_Evans (InterCall EMEA)"

 

[Gareth_Evans (InterCall EMEA)]

Thanks Bob, this is my first experience with RegEx and I'm afraid I'm failing
miserably. I've tried using your code but the cells that are highlighting
confuse me.

When I run the code it highlights cells which contain information but it's
different everytime, I can't see any correlation with the results.

Column L, Rows 1:13

(e-mail address removed)
(e-mail address removed)
d
..
c
d
(e-mail address removed)
@
..
...
..
..
..

The first three cells highlight but the second instances of "D" or
"(e-mail address removed)" don't highlight nor do any other cells, just the first
three.

I may not have explained my original request clearly, sorry if I didn't, but
I wanted to highlight cells in column L which aren't in a basic email format
(ie: (e-mail address removed) etc).
So cells without the @ sign and without at least one dot would be the very
least I'd like to highlight (I know email format's can vary wildly).

Best regards,

Gareth

 

[Gareth_Evans (InterCall EMEA)]

Ok, a slight re-work to my approach. I'm going to simply validate for the
following:

In column L each cell will be highlighted red unless it meets this criteria:

1 @ sign. No more, no less.
Between 1 and 4 dots/full stops
Between 0 and 3 hyphens

I'm going to try using the conditional format option and work record a macro
from this, hopefully I'll make enough sense of the code in relation to my
actions and edit this to suit my needs

Will post my results.