如何用VBA编写Excel宏校验邮箱格式标记错误，支持后续清除颜色标记

提供两个独立VBA宏，分别实现错误邮箱标记、标记批量清除功能，针对大数据量做了性能优化，全程仅修改单元格填充色，不会改动原有数据内容。

完整VBA代码 #

' 标记不符合规则的邮箱为紫色
Sub 标记错误邮箱()
    Dim lastRow As Long, i As Long
    Dim mail As String, firstName As String, lastName As String
    Dim isError As Boolean
    Const START_ROW As Integer = 2 ' 表格有表头设为2，无表头改为1
    
    ' 关闭非必要功能提升运行速度
    On Error GoTo Cleanup
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    
    ' 动态获取G列最后一行有效数据
    lastRow = Cells(Rows.Count, "G").End(xlUp).Row
    
    For i = START_ROW To lastRow
        mail = LCase(Cells(i, "G").Value)
        firstName = LCase(Trim(Cells(i, "E").Value))
        lastName = LCase(Trim(Cells(i, "F").Value))
        isError = False
        
        ' 三个判断规则，任意满足其一就标记为错误
        If firstName <> "" And InStr(mail, firstName) = 0 Then isError = True
        If lastName <> "" And InStr(mail, lastName) = 0 Then isError = True
        If InStr(mail, "@email.com") = 0 Then isError = True
        
        ' 赋值填充色，正确邮箱自动清除原有标记
        If isError Then
            Cells(i, "G").Interior.Color = RGB(128, 0, 128) ' 标准紫色，可自行调整RGB参数修改颜色
        Else
            Cells(i, "G").Interior.ColorIndex = xlColorIndexNone
        End If
    Next i
    
Cleanup:
    ' 恢复Excel默认设置
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    If Err.Number <> 0 Then MsgBox "运行出错：" & Err.Description, vbExclamation
End Sub

' 一键清除所有G列邮箱标记颜色
Sub 清除邮箱标记颜色()
    Dim lastRow As Long
    Const START_ROW As Integer = 2 ' 和上方标记宏的起始行保持一致
    On Error GoTo Cleanup
    Application.ScreenUpdating = False
    
    lastRow = Cells(Rows.Count, "G").End(xlUp).Row
    Range("G" & START_ROW & ":G" & lastRow).Interior.ColorIndex = xlColorIndexNone
    
Cleanup:
    Application.ScreenUpdating = True
    If Err.Number <> 0 Then MsgBox "运行出错：" & Err.Description, vbExclamation
End Sub

使用说明 #

• 部署：按Alt+F11打开VBA编辑器，右键点击当前工作簿→插入→模块，把上述代码粘贴到模块窗口，保存文件为启用宏的工作簿(*.xlsm)格式即可。
• 运行：在Excel「开发工具」选项卡点击「宏」，选择对应宏名执行即可，也可以把两个宏添加到快速访问栏实现一键操作。
• 适配：如果邮箱规则需要调整，直接修改For循环内的判断条件即可。

内容的提问来源于stack exchange，提问作者Bonita