Stuck with block if without end if, totally lost.

[lake1069]

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

EndWith



If Intersect(Target, Range("N10"))IsNothingThenExitSub

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

EndIf


Range("A1").SelectEnd Sub

[Paul_Hossler]

I added CODE tags to format your macro - you can use the [#] icon to insert them

After inserting missing spaces and indenting, I'm guessing you probably wanted something like this




Option Explicit


Sub Worksheet_Change(ByVal Target As Range)


If Target.Cells(1, 1).Address = "$E$10" Then
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
End If


ElseIf Target.Cells(1, 1).Address = "$N$10" Then
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
End If


Range("A1").Select
End Sub



But I think you could simplify



Option Explicit




Sub Worksheet_Change(ByVal Target As Range)

Dim shpOval As Shape
Dim r As Range

Set r = Target.Cells(1, 1)


If r.Address = "$E$10" Then
Set shpOval = ActiveSheet.Shapes("Oval 1")
ElseIf r.Address = "$N$10" Then
Set shpOval = ActiveSheet.Shapes("Oval 2")
Else
Exit Sub
End If

If r.Value >= -0.1 And r.Value <= 0.1 Then
shpOval.Fill.ForeColor.RGB = RGB(0, 176, 80)
ElseIf r.Value >= -0.29 And r.Value < 0.29 Then
shpOval.Fill.ForeColor.RGB = RGB(255, 255, 0)
Else
shpOval.Fill.ForeColor.RGB = RGB(255, 0, 0)
End If


Range("A1").Select
End Sub

[SamT]

My style, working from Paul's code
Option Explicit

Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells(1, 1).Address = "$E$10" Then ColorShape Target, "Oval 1"
If Target.Cells(1, 1).Address = "$N$10" Then ColorShape Target, "Oval 2"

Range("A1").Select
End Sub


Private Sub ColorShape(ByVal Target As Range, ShpName As String)
If Target.Value >= -0.1 And Target.Value <= 0.1 Then
ActiveSheet.Shapes.Range(Array(ShpName)).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(ShpName)).Select
With Selection.ShapeRange.Fill
.ForeColor.RGB = RGB(255, 255, 0)
End With
Else
ActiveSheet.Shapes.Range(Array(ShpName)).Select
With Selection.ShapeRange.Fill
.ForeColor.RGB = RGB(255, 0, 0)
End With
End If
End Sub

[snb]

Always avoid 'Select' in VBA.


Sub Worksheet_Change(ByVal Target As Range)
If InStr("$E$10$N$10", Target.Address) = 0 Then Exit Sub

y = Target.Value
With ActiveSheet.Shapes("Oval " & 1 - (Target.Address = "$N$10")).Fill.ForeColor
.RGB = RGB(255, 255 * -(y >= -0.29 And y < 0.29), 0)
If y >= -0.1 And y <= 0.1 Then .RGB = RGB(0, 176, 80)
End With
End Sub