基于计算值的单元格更新形状颜色

Scenario: I have a dashboard with tiles for things such as "Overdue Tasks". I have a shape called tileOverdueTasks. On top of it I have a wordart with a value of =Z11. Cell Z11 is calculated based on values in a separate sheet (Sheet5 (Tasks)). If Tasks.Z11 = 0 I want tileOverdueTasks to be green. If it's anything else, I want it to be red.

场景:我有一个包含块的仪表板，用于诸如“过期任务”。我有一种形状叫做磁贴。上面有一个值为=Z11的wordart。单元Z11是根据独立表(Sheet5(任务)中的值计算的。如果任务。Z11 = 0，我要把tileoverduetask变成绿色。如果是别的，我想要它是红色的。

I found the code below but it does not seem to trigger, I'm assuming due to the fact that cell Z11 is not being changed manually and thus neither is the ActiveWorkbook. So, how can I modify this to work given the scenario above?

我找到了下面的代码，但它似乎没有触发，我假设是因为没有手动更改cell Z11，因此ActiveWorkbook也没有。那么，考虑到上面的场景，我如何修改它以使其工作呢?

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("Z11")) Is Nothing Then
        If IsNumeric(Target.Value) Then
            If Target.Value > 0 Then
                ActiveSheet.Shapes("tileOverdueTasks").Fill.BackColor.RGB = vbRed
            Else
                ActiveSheet.Shapes("tileOverdueTasks").Fill.BackColor.RGB = vbGreen
            End If
        End If
    End If
End Sub

UPDATE: I have changed the event to Worksheet.calculate as suggested by @Scott Craner. Now the event fires and it even enters the proper condition, however the color is not changing.

更新:我已经将事件更改为工作表。按@Scott Craner的建议计算。现在事件触发，它甚至进入适当的条件，但是颜色没有改变。

1 个解决方案

#1

There were two problems.

有两个问题。

1) As pointed out by Scott Craner, I needed to use the Worksheet_Calculate event.

1)正如Scott Craner指出的，我需要使用Worksheet_Calculate事件。

2) Due to the style of the shape, Fill.BackColor was not working. I changed it to Fill.ForeColor and now it works.

2)由于造型的风格，填充。背景色是不工作。我把它改成了填充。现在它可以工作了。

Here is the working code:

以下是工作代码:

Private Sub Worksheet_Calculate()
    If Sheets("Dashboard").Range("Z11").Value > 0 Then
        Sheets("Dashboard").Shapes("tileOverdueTasks").Fill.ForeColor.RGB = RGB(185, 0, 0)
    Else
        Sheets("Dashboard").Shapes("tileOverdueTasks").Fill.ForeColor.RGB = RGB(0, 185, 0)
    End If
End Sub

#1