A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Highlight Cell that contains invalid email format



 
 
Thread Tools Display Modes
  #1  
Old March 11th, 2010, 06:43 PM posted to microsoft.public.excel.worksheet.functions
Gareth_Evans (InterCall EMEA)[_2_]
external usenet poster
 
Posts: 18
Default Highlight Cell that contains invalid email format

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:













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:
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
  #2  
Old March 11th, 2010, 07:03 PM posted to microsoft.public.excel.worksheet.functions
Bob Phillips[_3_]
external usenet poster
 
Posts: 489
Default Highlight Cell that contains invalid email format

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)"
m wrote in message
...
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:













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:
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



  #3  
Old March 12th, 2010, 03:49 PM posted to microsoft.public.excel.worksheet.functions
Gareth_Evans (InterCall EMEA)[_2_]
external usenet poster
 
Posts: 18
Default Highlight Cell that contains invalid email format

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



d
..
c
d

@
..
...
..
..
..

The first three cells highlight but the second instances of "D" or
" 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:
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




  #4  
Old March 12th, 2010, 06:25 PM posted to microsoft.public.excel.worksheet.functions
Gareth_Evans (InterCall EMEA)[_2_]
external usenet poster
 
Posts: 18
Default Highlight Cell that contains invalid email format

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.
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:09 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.