当目标单元格填充有参考单元格值时，Excel VBA Sub无法执行

debugcn 发表于 Dev

麻袋过长

当我手动输入“ b37”的值时，这对我希望它执行的操作非常有用，但是当我将“ = c20”放入“ b37”时，什么也没发生。当引用的单元格“ c20”中的值发生更改而不是手动输入“ b37”的值时，如何进行此项工作？谢谢！

Sub Worksheet_Change(ByVal target As Range)
    If Intersect(target, Range("b37")) Is Nothing Then Exit Sub
    If IsNumeric(target.Value) Then
        If target.Value < 0.95 Then
            ActiveSheet.Shapes("Straight Connector 1").Line.ForeColor.RGB = vbRed
        ElseIf target.Value >= 0.95 And target.Value < 1 Then
            ActiveSheet.Shapes("Straight Connector 1").Line.ForeColor.RGB = vbGreen
        Else
            ActiveSheet.Shapes("Straight Connector 1").Line.ForeColor.RGB = vbYellow
        End If
    End If
End Sub

麻袋过长

Private Sub Worksheet_Calculate()
    SetLineColor1 Me.Range("B37"), Me.Shapes("Line 1")
    SetLineColor2 Me.Range("D35"), Me.Shapes("Line 2")
End Sub

Sub SetLineColor1(c As Range, ln As Shape)
    Dim v, clr As Long

    v = c.Value
    If Not IsNumeric(v) Or Len(v) = 0 Then Exit Sub

    If v < 0.95 Then
        clr = vbRed
    ElseIf v >= 0.95 And v < 1 Then
        clr = vbGreen
    Else
        clr = vbYellow
    End If
    ln.Line.ForeColor.RGB = clr
End Sub
Sub SetLineColor2(c As Range, ln As Shape)
    Dim v, clr As Long

    v = c.Value
    If Not IsNumeric(v) Or Len(v) = 0 Then Exit Sub

    If v < 88 Then
        clr = vbRed
    ElseIf v >= 88 And v < 100 Then
        clr = vbGreen
    Else
        clr = vbYellow
    End If
    ln.Line.ForeColor.RGB = clr
End Sub

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。