VBA:基于计时器的条件格式化单元格颜色，具有自己的条件

Question

我在文本框中有一个计时器，随着计时器计数，它将从绿色变为黄色和红色。颜色更改的时间间隔由单元格中下拉列表中的选择决定。

问题：如何让周围的细胞也改变颜色？

If StopWatch.Range("E3") = "Anodized" Then

If Calculations.Range("A1").Value > Calculations.Range("B3") And _
Calculations.Range("A1").Value <= Calculations.Range("B4") Then
With StopWatch.Shapes("TimeBox")
.Fill.ForeColor.RGB = RGB(0, 255, 0) 'Green
End With
Else
If Calculations.Range("A1").Value > Calculations.Range("B4") And _
Calculations.Range("A1").Value <= Calculations.Range("B5") Then
With StopWatch.Shapes("TimeBox")
.Fill.ForeColor.RGB = RGB(255, 255, 0) 'Yelow
End With

Else
If Calculations.Range("A1").Value > Calculations.Range("B5") Then
With StopWatch.Shapes("TimeBox")
.Fill.ForeColor.RGB = RGB(255, 0, 0) 'Red
End With
End If
End If
End If
End If

上面的代码是我目前拥有的条件格式的示例。我对下拉框中的其他几个选项重复了这一点。

Answer 1

将我的评论扩展为完整的答案...

使用StopWatch.Shapes("TimeBox").TopLeftCell.Offset(ROWS,COLUMNS).Resize(ROWS,COLUMNS)引用周围的单元格。例如，如果我想要TimeBox周围的每个相邻单元格，我可以像StopWatch.Shapes("TimeBox").TopLeftCell.Offset(-1,-1).Resize(3,3)一样引用它们，这将在TimeBox的左上角返回3x3范围。

这是应用此代码的代码。我还将您的 If/Else 更改为看起来更干净的选择案例。

Dim LowerLimit As Variant, MidLimit As Variant, UpperLimit As Variant
LowerLimit = Calculations.Range("B3").Value
MidLimit = Calculations.Range("B4").Value
UpperLimit = Calculations.Range("B5").Value

Dim TimeBox As Shape, SurroundingRange As Range
Set TimeBox = StopWatch.Shapes("TimeBox")
Set SurroundingRange = TimeBox.TopLeftCell.Offset(-1, -1).Resize(3, 3) 'Edit this range as neccesary

If StopWatch.Range("E3") = "Anodized" Then
Select Case Calculations.Range("A1").Value
Case LowerLimit To MidLimit
TimeBox.Fill.ForeColor.RGB = RGB(0, 255, 0) 'Green
SurroundingRange.Interior.Color = RGB(0, 255, 0)
Case MidLimit To UpperLimit
TimeBox.Fill.ForeColor.RGB = RGB(255, 255, 0) 'Yellow
SurroundingRange.Interior.Color = RGB(255, 255, 0)
Case Is > UpperLimit
TimeBox.Fill.ForeColor.RGB = RGB(255, 0, 0) 'Red
SurroundingRange.Interior.Color = RGB(255, 0, 0)
End Select
End If