时间戳

首页 > 解决方案 > 用一些vba代码写，死路一条！得到错误结束块结束如果没有结束如果

excel - 用一些vba代码写，死路一条！得到错误结束块结束如果没有结束如果

问题描述

我正在创建一个仪表板。我有两个形状椭圆 1 和椭圆 2。它们将根据特定单元格的值改变颜色

我收到一个错误：

阻塞 if 没有 end if

我在这里做错了什么！

Sub Worksheet_Change(ByVal Target As Range)
'
    If Intersect(Target, Range("E10")) Is Nothing Then Exit Sub

        If Target.Value >= -0.1 And Target.Value <= 0.1 Then
            ActiveSheet.Shapes.Range(Array("Oval 1")).Select
                With Selection.ShapeRange.Fill
                .ForeColor.RGB = RGB(0, 176, 80)
                End With
        ElseIf Target.Value >= -0.29 And Target.Value < 0.29 Then
            ActiveSheet.Shapes.Range(Array("Oval 1")).Select
                With Selection.ShapeRange.Fill
                .ForeColor.RGB = RGB(255, 255, 0)
                End With
        Else
             ActiveSheet.Shapes.Range(Array("Oval 1")).Select
                With Selection.ShapeRange.Fill
                .ForeColor.RGB = RGB(255, 0, 0)

                End With



        If Intersect(Target, Range("N10")) Is Nothing Then Exit Sub

        If Target.Value >= -0.1 And Target.Value <= 0.1 Then
            ActiveSheet.Shapes.Range(Array("Oval 2")).Select
                With Selection.ShapeRange.Fill
                .ForeColor.RGB = RGB(0, 176, 80)
                End With
        ElseIf Target.Value >= -0.29 And Target.Value < 0.29 Then
            ActiveSheet.Shapes.Range(Array("Oval 2")).Select
                With Selection.ShapeRange.Fill
                .ForeColor.RGB = RGB(255, 255, 0)
                End With
        Else
             ActiveSheet.Shapes.Range(Array("Oval 2")).Select
                With Selection.ShapeRange.Fill
                .ForeColor.RGB = RGB(255, 0, 0)
                End With

    End If


    Range("A1").Select
End Sub

标签： excelvbadashboard

解决方案

重构：

Sub Worksheet_Change(ByVal Target As Range)

    If Target.CountLarge > 1 Then Exit Sub

    If Not Intersect(Target, Me.Range("E10")) Is Nothing Then
        Me.Shapes.Range("Oval 1").ShapeRange.Fill.ForeColor.RGB = ValueColor(Target.Value)
    End If

    If Not Intersect(Target, Me.Range("N10")) Is Nothing Then
        Me.Shapes.Range("Oval 2").ShapeRange.Fill.ForeColor.RGB = ValueColor(Target.Value)
    End If

End Sub

Function ValueColor(v) As Long
    Dim rv As Long
    If v > -0.1 And v <= 0.1 Then
        rv = RGB(0, 176, 80)
    ElseIf v.Value >= -0.29 And v.Value < 0.29 Then
        rv = RGB(255, 255, 0)
    Else
        rv = RGB(255, 0, 0)
    End If
    ValueColor = rv
End Function

推荐阅读