如何制作宏以相对单元格引用应用三色刻度条件格式

我的C列是初始余额，D列是剩余余额，要给D列每个单元格应用三色刻度（绿色→黄色→红色），但每个D列单元格的颜色阈值必须基于对应行C列的初始余额计算（比如C列值的10%、65%、85%）。Excel条件格式不支持相对引用，没法直接复制规则，手动设置200多次太麻烦。我刚学VBA一周，用宏录制改了代码，但运行后只有三色刻度规则，阈值的计算公式没生效，效果不对。

原代码如下：

Dim wsT As Worksheet
 
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

Set wsT = ThisWorkbook.Worksheets("Tracking")

For X = 5 To LastRow

wsT.Range("D" & X).FormatConditions.AddColorScale ColorScaleType:=3
wsT.Range("D" & X).FormatConditions(wsT.Range("D" & X).FormatConditions.Count).SetFirstPriority
wsT.Range("D" & X).FormatConditions(1).ColorScaleCriteria(1).Type = _
    xlConditionValueFormula
wsT.Range("D" & X).FormatConditions(1).ColorScaleCriteria(1).Value = _
    wsT.Range("C" & X) * 0.1

With wsT.Range("D" & X).FormatConditions(1).ColorScaleCriteria(1).FormatColor
    .Color = 7039480
    .TintAndShade = 0
End With

wsT.Range("D" & X).FormatConditions(1).ColorScaleCriteria(2).Type = _
    xlConditionValueFormula
wsT.Range("D" & X).FormatConditions(1).ColorScaleCriteria(2).Value = _
    wsT.Range("C" & X) * 0.65

With wsT.Range("D" & X).FormatConditions(1).ColorScaleCriteria(2).FormatColor
    .Color = 8711167
    .TintAndShade = 0
End With

wsT.Range("D" & X).FormatConditions(1).ColorScaleCriteria(3).Type = _
    xlConditionValueFormula
wsT.Range("D" & X).FormatConditions(1).ColorScaleCriteria(3).Value = _
    wsT.Range("C" & X) * 0.85

With wsT.Range("D" & X).FormatConditions(1).ColorScaleCriteria(3).FormatColor
    .Color = 8109667
    .TintAndShade = 0
End With

Next X
End Sub

问题原因 #

原代码给Value赋值的是计算后的固定数值，而不是Excel公式。当Type设为xlConditionValueFormula时，需要传入带等号的字符串公式，让Excel动态引用对应行的C列单元格，而不是直接计算出值。

修正后的代码 #

Sub ApplyRowSpecificColorScales()
    Dim wsT As Worksheet
    Dim LastRow As Long
    Dim X As Long
    Dim cfColorScale As FormatCondition
    
    Set wsT = ThisWorkbook.Worksheets("Tracking")
    LastRow = wsT.Cells(wsT.Rows.Count, "A").End(xlUp).Row ' 限定在目标工作表内查找最后一行
    
    For X = 5 To LastRow
        ' 添加三色刻度并获取引用，简化代码路径
        Set cfColorScale = wsT.Range("D" & X).FormatConditions.AddColorScale(ColorScaleType:=3)
        cfColorScale.SetFirstPriority
        
        ' 第一个阈值：C列值的10%，绿色
        With cfColorScale.ColorScaleCriteria(1)
            .Type = xlConditionValueFormula
            .Value = "=$C$" & X & "*0.1" ' 传入带等号的字符串公式，动态引用对应行C列
            .FormatColor.Color = 7039480
            .FormatColor.TintAndShade = 0
        End With
        
        ' 第二个阈值：C列值的65%，黄色
        With cfColorScale.ColorScaleCriteria(2)
            .Type = xlConditionValueFormula
            .Value = "=$C$" & X & "*0.65"
            .FormatColor.Color = 8711167
            .FormatColor.TintAndShade = 0
        End With
        
        ' 第三个阈值：C列值的85%，红色
        With cfColorScale.ColorScaleCriteria(3)
            .Type = xlConditionValueFormula
            .Value = "=$C$" & X & "*0.85"
            .FormatColor.Color = 8109667
            .FormatColor.TintAndShade = 0
        End With
    Next X
End Sub

代码说明 #

• 修正LastRow获取逻辑，限定在目标工作表内操作，避免当前活动表非Tracking时出错
• 用cfColorScale变量存储条件格式对象，简化重复引用的代码路径
• 核心修正：给Value传入带等号的字符串公式（如"=$C$" & X & "*0.1"），让Excel动态读取对应行C列的值计算阈值，而非固定数值
• 用With语句简化同对象的属性设置，代码更整洁

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