我正在创建一个记分卡来跟踪每周报告的问题数量。
我希望添加格式
- 如果先前的值大于当前值,则应以红色突出显示
- 如果先前的值小于当前值,则应以绿色突出显示。
我在一个表格中进行了此设置,其中列出了每周的值。
答案1
答案2
您可以将其放入工作表的代码中:它仅更改 C2 和 C100 之间的颜色,但您可以更改代码中的列和范围以适应。
Private Sub Worksheet_Change(ByVal target As Excel.Range)
Dim MyRnge As Range, c As Range
Set MyRnge = Range("$C$2:$C$100")
If target.Column = 3 Then
For Each c In MyRnge
If c.Value > c.Offset(-1, 0).Value Then c.Interior.ColorIndex = 4
If c.Value < c.Offset(-1, 0).Value Then c.Interior.ColorIndex = 3
Next c
End If
End Sub
'------------------------------------ version 2
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRnge As Range, c As Range
Set MyRnge = Range("$A$117:$H$118")
If Target = Range("$A$117") Then
For Each c In MyRnge
If c.Value > Target.Value Then c.Interior.ColorIndex = 4
If c.Value < Target.Value Then c.Interior.ColorIndex = 3
Next c
End If
End Sub
'---------------version 3
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRnge As Range, c As Range, mycell As Range
Set MyRnge = Range(Cells(5, 2), Cells(5, Cells(5, Columns.Count).End(xlToLeft).Column))
If Not Intersect(Target, MyRnge) Is Nothing Then
For Each c In MyRnge
If c.Value > c.Offset(-1, 0).Value Then c.Interior.ColorIndex = 4
If c.Value < c.Offset(-1, 0).Value Then c.Interior.ColorIndex = 3
Next c
End If
End Sub