CODEWITHSUNDEEP
excelvbavalidationemail
Emily Strzelecki
Emily Strzelecki

Reputation: 23

Adding Conditional formatting with email validation macro

I originally found this code here link and it works great. The call function is in cell G56. I am wanting to add another level to it though and have it highlight the merged cell ("B56:F56") green or red based off the validation being true/false.

I was going to try and ask the OP if they had a solution but the site won't let me. I've also tried placing conditional formatting on the cells for when they're true/false to change color but that's not working.

This is in module 1 

Function ValidateEmail(emailAddr As String) As Boolean

    Dim findAt As Long
    Dim findLastDot As Long
    Dim findSpace As Long

'   Default validate is true
    ValidateEmail = True 
                         '<<tried adding then range color selection here no luck 

    findAt = InStr(emailAddr, "@")
    findLastDot = InStrRev(emailAddr, ".")
    findSpace = InStr(emailAddr, " ")

'   Check for characters
    If (findAt = 0) Or (findLastDot = 0) Or (findSpace > 0) Then
        ValidateEmail = False
        Exit Function
    End If

'   Make sure amperand not first space
    If findAt = 1 Then
         ValidateEmail = False
        Exit Function
    End If

'   Make sure the last dot comes at least two spots after the ampersand
    If (findLastDot - 1) <= findAt Then
        ValidateEmail = False
        Exit Function
    End If

End Function

Upvotes: 0

Views: 111

Answers (1)

Tim Williams
Tim Williams

Reputation: 166511

You can apply the CF to the merged cell but use the return value from your VBA formula to drive the formatting (using a formula-based CF rule)

enter image description here

Upvotes: 2

Related Questions