Excel如何校验Sheet1仅含Sheet2定义的允许字符并高亮违规单元格

根据你的文件结构，以下两种方案都可以实现需求，优先推荐第一种无宏方案，兼容性更好。

方案1：条件格式实现（无需编写代码，推荐） #

这个方案不需要启用宏，文件可以保存为普通.xlsx格式，操作步骤如下：

• 第一步：定义允许字符的固定名称
  切换到Sheet2，选中所有存放允许字符的单元格（跳过表头AllowedCharacters，比如字符存在A2到A78区域，就选中A2:A78），点击窗口左上角的名称框，输入AllowedChars后按回车键，给这个区域设置固定引用名。
• 第二步：选中待检测区域
  切回Sheet1，选中所有需要检测的原始数据单元格，注意要跳过第一行表头，选中后保证区域左上角的第一个单元格为活动状态（比如数据从A2开始，活动单元格就停在A2）。
• 第三步：设置条件格式规则
  点击顶部菜单栏「开始」→「条件格式」→「新建规则」，选择规则类型为使用公式确定要设置格式的单元格，在公式输入框填入以下内容：

  =SUMPRODUCT(--ISERR(SEARCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),AllowedChars&"")))>0
  

  注意公式里的A2要和你选中区域的活动单元格位置一致，不要加$绝对引用符号。
  接着点击公式下方的「格式」按钮，切换到「填充」标签页，选择黄色作为高亮色，依次点击确定保存规则即可。
  规则生效后，所有包含不在允许列表内字符的单元格都会自动填充黄色，比如示例中Mark地址栏的*不在允许列表中，就会被自动标黄。

方案2：VBA宏实现（适合大数据量场景） #

如果你的数据量过万行，条件格式计算会有卡顿，可以用VBA一键批量检测：

• 按Alt+F11快捷键打开VBA编辑器，在左侧工程资源管理器里右键点击当前工作簿名称，选择「插入」→「模块」。
• 在弹出的模块代码编辑窗口粘贴以下代码：

  Sub 检测非法字符并标黄()
      Dim wsData As Worksheet, wsAllowed As Worksheet
      Dim allowedRange As Range, targetCell As Range
      Dim allowedChars As Variant, i As Long, hasInvalid As Boolean
      ' 绑定工作表，可根据实际表名修改
      Set wsData = ThisWorkbook.Sheets("Sheet1")
      Set wsAllowed = ThisWorkbook.Sheets("Sheet2")
      ' 读取所有允许字符到数组，提升计算速度
      Set allowedRange = wsAllowed.Range("A2:A" & wsAllowed.Cells(wsAllowed.Rows.Count, "A").End(xlUp).Row)
      allowedChars = allowedRange.Value
      ' 清除之前的黄色填充
      wsData.UsedRange.Interior.ColorIndex = xlNone
      ' 遍历所有非空数据单元格（跳过第一行表头）
      For Each targetCell In wsData.UsedRange.Offset(1, 0).SpecialCells(xlCellTypeConstants)
          hasInvalid = False
          For i = 1 To Len(targetCell.Value)
              ' 逐字符匹配允许列表
              If IsError(Application.Match(Mid(targetCell.Value, i, 1), allowedChars, 0)) Then
                  hasInvalid = True
                  Exit For
              End If
          Next i
          ' 存在非法字符则填充黄色
          If hasInvalid Then targetCell.Interior.Color = vbYellow
      Next targetCell
      MsgBox "检测完成，非法字符单元格已标黄"
  End Sub
  

• 按F5运行宏，等待执行完成即可得到标黄结果，运行前可以根据实际的表名、表头位置调整代码里的对应参数。

注意事项：如果你的合法字符包含半角/全角空格，一定要把对应的空格单独添加到Sheet2的允许字符列表中，否则会出现误判。

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