Excel动态命名区域单元格对比及条件上色问题求助

需要针对Excel中的动态命名区域，判断区域内每个单元格是否大于上方单元格，或值是否相同，并据此设置单元格填充颜色。但每次刷新后命名区域范围会变化，无法使用静态单元格地址，尝试过IF函数、单元格偏移等方法均未解决。

Dim Col0 As Long
Dim Col1 As Long
Dim Col2 As Long
Dim Col3 As Long
Dim Col4 As Long
Dim Col5 As Long
Dim Col6 As Long
Dim Col7 As Long

c = 0

Col0 = RGB(142, 169, 219)
Col1 = RGB(213, 184, 234)
Col2 = RGB(255, 217, 102)
Col3 = RGB(169, 208, 142)
Col4 = RGB(244, 176, 132)
Col5 = RGB(180, 238, 210)
Col6 = RGB(208, 215, 145)
Col7 = RGB(167, 127, 225)

WS.Range("C9").Interior.Color = Col1

Dim tms As Range
pr = 0
pr = ActiveSheet.Range("B15", ActiveSheet.Cells(Rows.Count, "B").End(xlUp)).Count
Set tms = Application.Range("B15:B" & 14 + pr)

Dim First As Integer
Dim Last As Integer

First = 15
Last = 15 + pr - 1

Debug.Print First
Debug.Print Last

If First <> Last Then
    WS.Range("B" + First).Interior.Color = Col1
Else
    Cells(k, 3).Value = "Same"
End If

Set Cel1 = WS.Range("B15")

'Col0 = "14395790"
'Col1 = "15382741"
'Col2 = "6740479"
'Col3 = "9359529"
'Col4 = "8696052"
'Col5 = "13823668"
'Col6 = "9557968"
'Col7 = "14778279"

t = 0

'MsgBox pr







'tms.Interior.Color = Col1

'For Each cel In tms.Cells

'If Cel1.Value < Cel2.Value

'    Cel1.Interior.Color = "Col" & c
'    c = c + 1
'    End If

't = t + 1

'Next cel
'On Error Resume Next

1. 直接引用动态命名区域：无需手动计算范围，通过ThisWorkbook.Names("你的动态区域名称").RefersToRange直接获取最新的动态区域对象，自动适配刷新后的范围变化。
2. 单元格偏移对比：遍历区域中从第二行开始的每个单元格，用cel.Offset(-1, 0)直接定位上方单元格，完成值的对比。
3. 颜色数组优化：把预设颜色存入数组，替代单个变量定义，方便循环调用，示例：Dim colors As Variant: colors = Array(RGB(142,169,219), RGB(213,184,234), ...)。
4. 核心逻辑示例：
   • 先清除动态区域原有填充色，避免残留样式；
   • 遍历区域内非首行的单元格，根据与上方单元格的值对比结果设置颜色：

     Dim dynamicRng As Range
     Set dynamicRng = ThisWorkbook.Names("DynamicRange").RefersToRange
     
     ' 清除原有颜色
     dynamicRng.Interior.ColorIndex = xlColorIndexNone
     
     ' 遍历区域（跳过首行）
     Dim cel As Range
     For Each cel In dynamicRng.Cells
         If cel.Row > dynamicRng.Row Then
             If cel.Value > cel.Offset(-1, 0).Value Then
                 cel.Interior.Color = RGB(213, 184, 234) ' 自定义颜色
             ElseIf cel.Value = cel.Offset(-1, 0).Value Then
                 cel.Interior.Color = RGB(142, 169, 219) ' 自定义颜色
             End If
         End If
     Next cel
     

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