The problem with your original code is that using xlCellValue for comparison triggers Excel's implicit type coercion—even if your cells are stored as text, Excel will try to compare them numerically if the values look like numbers. To force an exact text match, you need to use a formula-based conditional format instead, which lets you explicitly compare text values.

Here's the corrected code:

' c代表列号，lRow代表最后一行
' RefVal是从参考工作表读取的字符串变量
Dim targetRange As Range
Set targetRange = Range(Cells(2, c), Cells(lRow, c))

' 清除现有条件格式（可选，避免重复规则堆积）
targetRange.FormatConditions.Delete

' 添加基于公式的条件格式：检查单元格文本与RefVal是否不相等
With targetRange.FormatConditions.Add(Type:=xlExpression, Formula1:= _
    "=RC<>""" & RefVal & """")
    .Interior.ColorIndex = 6 ' 设置黄色背景
End With

Why this works: #

• xlExpression tells Excel to evaluate a custom formula instead of doing a default cell value comparison.
• The formula =RC<>""" & RefVal & """ translates to something like =A2<>"123" in Excel (depending on your column). The doubled quotes ("") in VBA escape the single quotes needed in the Excel formula, ensuring RefVal is treated as a literal text string rather than a numeric value.
• RC uses R1C1 notation to reference the current cell relative to the range, so every cell in the column checks its own value against RefVal correctly.

Quick Notes: #

• If you prefer A1 notation, you could adjust the formula to ="CHAR(65+" & c-1 & ")"" & ROW()<>""" & RefVal & """, but R1C1 is cleaner for column-wide ranges.
• Double-check that RefVal is retrieved as a string (use CStr() if needed) to avoid unexpected formatting glitches.

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