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. |
|
|
Thread Tools | Display Modes |
#2
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|