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