writing with some vba code, hit a dead end! getting error end block end if without end if

Question

I'm creating a dashboard. I've got two shapes oval 1 and oval 2. They are to change colors depending on the value of specific cells

I'm getting an error:

block if without end if

What am i doing wrong here!

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

You're missing an `End If` before `If Intersect(Target, Range("N10")) Is Nothing Then Exit Sub`. — BigBen, Jan 14 '20 at 21:20
This is begging for some refactoring too. There's a good amount of duplicate code. — BigBen, Jan 14 '20 at 21:23
[this may be useful](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Also, properly indenting your code helps avoid this kind of error. See [this](http://rubberduckvba.com/Indentation) — cybernetic.nomad, Jan 14 '20 at 21:24
I added to End If, it made the error go away but its not working still. One shape changes color based on the value, the second one does not (oval 2) I just last semester and im still learning, im sure the code could be much better — Oscar Nuñez, Jan 14 '20 at 21:53
@OscarNuñez - if the code is not working, debug (step through line-by-line) and see what behavior it takes versus what you expect. — Scott Holtzman, Jan 14 '20 at 22:11
You're exiting the Sub twice based on range intersects - if the first one exits then it's not going to test the second one... — Tim Williams, Jan 14 '20 at 22:23
I still count on my fingers when matching braces or finding open/close block elements. Make a fist. Every time you see an "IF" that's a +1. Every time you see an End IF, that's a -1. If you've got fingers out at the end of the code, your END statements don't match your IFs — Ryan B., Jan 14 '20 at 22:24

score 2 · Accepted Answer · answered Jan 14 '20 at 22:31

Refactored:

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

writing with some vba code, hit a dead end! getting error end block end if without end if

1 Answers1