Office Excel：如何为多行设置无需重复公式的条件格式

一、条件格式批量设置方法（推荐） #

你之前的公式问题在于用了绝对引用固定单元格（如$C$2），导致所有单元格都只检查C2的值，而非当前单元格。正确的批量公式应该用相对引用当前单元格，配合固定的跨表数据范围：

操作步骤： #

1. 选中C2:C100整个单元格区域
2. 点击「开始」→「条件格式」→「新建规则」→选择「使用公式确定要设置格式的单元格」
3. 输入公式：

=COUNTIF(Sheet1!$B$2:$B$125, C2)=0

4. 点击「格式」按钮，设置提示样式（比如填充红色、字体加粗，方便识别不在列表中的值）
5. 确认保存即可

公式说明：

• Sheet1!$B$2:$B$125用绝对引用固定预定义列表范围
• C2是相对引用，选中区域内的每个单元格会自动对应为当前行的C列单元格（如C3、C4...）
• =0表示当当前单元格的值在列表中不存在时，触发格式

二、替代方案：数据验证（输入时主动限制） #

如果希望用户输入时就直接拦截无效值，可使用数据验证功能：

1. 选中C2:C100区域
2. 点击「数据」→「数据验证」→允许类型选择「自定义」
3. 输入公式：

=COUNTIF(Sheet1!$B$2:$B$125, C2)>=1

4. 切换到「出错警告」选项卡，设置提示内容（如“输入值不在预定义列表中，请重新输入”）
5. 确认后，用户输入不在列表中的内容会直接弹出警告

三、VBA自动验证方案 #

若需要更自动化的处理（比如单元格修改后自动标记），可添加工作表事件代码：

1. 右键点击当前工作表标签→选择「查看代码」
2. 粘贴以下代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 只处理C2:C100区域的修改
    If Not Intersect(Target, Range("C2:C100")) Is Nothing Then
        Dim cell As Range
        For Each cell In Intersect(Target, Range("C2:C100"))
            ' 检查当前单元格值是否在Sheet1的列表中
            If Application.WorksheetFunction.CountIf(Sheet1.Range("B2:B125"), cell.Value) = 0 Then
                cell.Interior.Color = vbRed ' 不在列表则标红
            Else
                cell.Interior.Color = xlNone ' 在列表则恢复默认格式
            End If
        Next cell
    End If
End Sub

3. 关闭VBA编辑器，返回工作表即可生效

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