lake1069
01-14-2020, 02:30 PM
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
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