0

I'm trying to make a macro that will go through a list of emails and flag potential problems by changing their color.

I was trying to do it by using the Like function, but I forgot that it's case sensitive and the resulting code looks kind of big and messy anyway. I know I could use UPPER() to solve it, but it's already a large messy looking code and I hoped to avoid that.

Is there a way I can use InStr() instead? I've seen people say it's a better option than Like and I've been trying to look up how this function works, but the explanations I've found online are difficult for me to follow.

To explain the code I have so far, it's designed to flag anything with multiple emails as Blue, anything that isn't an email as red, and anything within a list of popular public email domains (Gmail, yahoo, hotmail etc etc) as green. I've trimmed the list of domains down from several hundred to just three for simplification here.

Sub Email_Check()
    Do    
        If (ActiveCell Like "*@*@*") = True Then
            ActiveCell.Interior.ColorIndex = 8
        ElseIf (ActiveCell Like "*@*") = False Or (ActiveCell Like "*.*") = False = True _
        Then
            ActiveCell.Interior.ColorIndex = 3
        ElseIf ActiveCell Like "*gmail.com" = True Or ActiveCell Like "*hotmail.com" = True Or ActiveCell Like "*yahoo.com" = True _
        Then
            ActiveCell.Interior.ColorIndex = 4
        End If
        
        ActiveCell.Offset(1, 0).Range("A1").Select
    Loop Until Application.CountA(ActiveCell.EntireRow) = 0
End Sub
GSerg
  • 76,472
  • 17
  • 159
  • 346
MRDoubleyou
  • 45
  • 1
  • 8
  • 3
    `I forgot that it's case sensitive` - it's not. It [uses](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/like-operator#remarks) the specified text comparison mode, which is `Option Compare Binary` by default. Nothing stops you from making it `Option Compare Text`. The entire thing is achievable via [conditional formatting](https://support.microsoft.com/en-us/office/use-conditional-formatting-to-highlight-information-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f). – GSerg May 17 '23 at 14:49
  • I've never used option before. It does seem to work but it's strange that it sits outside the code. could it interfere with other macros attached to the File? – MRDoubleyou May 17 '23 at 15:13
  • 1
    It affects all macros in the same module, like [documented](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/option-compare-statement#remarks). – GSerg May 17 '23 at 15:19
  • If you are concerned about its impact on other macros then place this macro in its own module by itself with the Option Compare Text – CHill60 May 17 '23 at 15:23
  • 3
    `Or (ActiveCell Like "*.*") = False = True` looks *very* strange to me. – FunThomas May 17 '23 at 15:41

0 Answers0