具有不同参考单元格的同一宏的多个worksheet_change

Question

想要创建一些动态仪表板，其中的形状将随着参考单元格的值更改(基于某个阈值(而改变颜色。

法典：

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("M5")) Is Nothing Then Exit Sub
If IsNumeric(Target.Value) Then
If Target.Value < Range("$AA$5") Then
ActiveSheet.Shapes("Isosceles Triangle 3").Fill.ForeColor.RGB = vbRed
ElseIf Target.Value >= Range("$AA$5") And Target.Value < Range("$Y$5") Then
ActiveSheet.Shapes("Isosceles Triangle 3").Fill.ForeColor.RGB = vbYellow
ElseIf Target.Value >= Range("$Y$5") And Target.Value < Range("$Z$5") Then
ActiveSheet.Shapes("Isosceles Triangle 3").Fill.ForeColor.RGB = vbGreen
ElseIf Target.Value >= Range("$Z$5") And Target.Value < Range("$AB$5") Then
ActiveSheet.Shapes("Isosceles Triangle 3").Fill.ForeColor.RGB = vbYellow
Else
ActiveSheet.Shapes("Isosceles Triangle 3").Fill.ForeColor.RGB = vbRed
End If
End If   
End Sub

这对于仪表板中的一个三角形工作正常，具有一些定义的阈值(动态更改颜色(。还想用其他三角形复制相同的操作。怎么能做到这一点呢？

[！三角形序列][1]][1]

编辑和添加部分：

仪表板有多个品牌明智的表现可供展示。

示意图：[在此输入图像描述][2]

CFA，DB和SS级别的跨品牌的实际数据： 实际数据 [在此输入图像描述][3]

CFA、SS和Sub D的阈值水平： [在此输入图像描述][4]

因此，对于每个品牌(品牌 1、2 和 3(在库存点(CFA、DB 和 SS 级别(，颜色代码的行为如下：

If the actual< UCL2, the triangle will be red (e.g. Brand1 at CFA is 9, respective tringle will be red), UCL2<= actual Yellow , UCL1<= actual Green , LCL1<= actual Yellow , Actual =>LCL2 --> Red

希望现在的问题更加清晰。期待这方面的帮助...蒂亚

Answer 1

根据您的要求编辑

并进行了轻微的重构

Private Sub Worksheet_Change(ByVal Target As Range)
Colorize ActiveSheet.Shapes("Isosceles Triangle 1"), Target, Range, ("M1"), Range("$AA$5").Value, Range("$AB$5").Value, Range("$Y$5").Value, Range("$Z$5").Value
Colorize ActiveSheet.Shapes("Isosceles Triangle 2"), Target, Range("M3"), 19, 60, 32, 38
'Colorize ActiveSheet.Shapes("Isosceles Triangle 3"), Target, Range("M5")
End Sub
Private Sub Colorize(shp As Shape, ByVal Target As Range, rValue as Range, _
YellowLow As long, YellowHigh As Long, _
GreenLow As Long, GreenHigh As Long)
Dim iColor As Long
If Intersect(Target, rValue) Is Nothing Then Exit Sub
'If IsNumeric(Target.Value) Then
'    iColor = vbRed
'    If Target.Value < Range("$AA$5") Then
'        iColor = vbRed
'    ElseIf Target.Value >= Range("$AA$5") And Target.Value < Range("$Y$5") Then
'        iColor = vbYellow
'    ElseIf Target.Value >= Range("$Y$5") And Target.Value < Range("$Z$5") Then
'        iColor = vbGreen
'    ElseIf Target.Value >= Range("$Z$5") And Target.Value < Range("$AB$5") Then
'        iColor = vbYellow
'    End If
If IsNumeric(Target.Value) Then
iColor = vbRed
If Target.Value >= YellowLow And Target.Value <= YellowHigh Then  iColor = vbYellow
If Target.Value >= GreenLow And Target.Value <= GreenHigh Then    iColor = vbGreen
shp.Fill.ForeColor.RGB = iColor
End If  
End Sub