当目标单元格充满计算值时如何触发代码? [英] How to trigger code when target cell is filled with calculated value?

问题描述

当我手动输入"b37"的值时，此方法有效但是当我将"= c20"放入"b37"时，什么都没发生.

This works when I manually enter the value for "b37" but when I put "=c20", for example, into "b37" nothing happens.

当引用的单元格"c20"中的值等于0时，该如何工作?更改而不是手动输入"b37"的值?

How do I make this work for when the value in the cell referenced, "c20" changes instead of manually entering a value for "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

这篇关于当目标单元格充满计算值时如何触发代码?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！