Hello!
I am trying to create a macro in which the color changes on a shape depending on a value entered. My sheet has three circle shapes that is corresponding on a factor (Time, Speed and Accuracy). Currently the code works on a worksheet, but it seems that I got the logic wrong. Because sometimes there are inconsistencies about the shape colour. Maybe I got the condition wrong? Here's the guide for the color changes:
<tbody>
</tbody>
Here's the current code:
I would like to put the code on a button so whenever I press on it the color changes after I filled out the cells, but when I added it on a button an error appeared. "Procedure declaration does not match description of event or procedure having same name"
Here's the code I tried after I added a button
Any thoughts in fixing this macro would be greatly appreciated. Thank you!
I am trying to create a macro in which the color changes on a shape depending on a value entered. My sheet has three circle shapes that is corresponding on a factor (Time, Speed and Accuracy). Currently the code works on a worksheet, but it seems that I got the logic wrong. Because sometimes there are inconsistencies about the shape colour. Maybe I got the condition wrong? Here's the guide for the color changes:
Time | Speed | Accuracy | ||||
Green |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | |||
Yellow |
<tbody> </tbody> | |||||
Red |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody>
</tbody>
Here's the current code:
Code:
Private Sub WorksheetChange(ByVal Target As Range)
If Not Intersect(Target, Range("B5")) Is Nothing Then
Me.Shapes("Oval 1").Select
With Range("B5")
If .Value >= 189.5 Then
Selection.ShapeRange.Fill.ForeColor.RGB = vbGreen
ElseIf .Value <= 185 And .Value >= 189.4 Then
Selection.ShapeRange.Fill.ForeColor.RGB = vbYellow
ElseIf .Value <= 185.4 Then
Selection.ShapeRange.Fill.ForeColor.RGB = vbRed
End If
.Select
End With
End If
If Not Intersect(Target, Range("B6")) Is Nothing Then
Me.Shapes("Oval 2").Select
With Range("B6")
If .Value >= 49.5 Then
Selection.ShapeRange.Fill.ForeColor.RGB = vbGreen
ElseIf .Value < 49.5 Then
Selection.ShapeRange.Fill.ForeColor.RGB = vbRed
End If
.Select
End With
End If
If Not Intersect(Target, Range("B7")) Is Nothing Then
Me.Shapes("Oval 3").Select
With Range("B7")
If .Value > 5 Then
Selection.ShapeRange.Fill.ForeColor.RGB = vbGreen
ElseIf .Value <5
Selection.ShapeRange.Fill.ForeColor.RGB = vbRed
End If
.Select
End With
End If
End Sub
I would like to put the code on a button so whenever I press on it the color changes after I filled out the cells, but when I added it on a button an error appeared. "Procedure declaration does not match description of event or procedure having same name"
Here's the code I tried after I added a button
Code:
Private Sub CommandButton1_Click(ByVal Target As Range)
If Not Intersect(Target, Range("B5")) Is Nothing Then
Me.Shapes("Oval 1").Select
With Range("B5")
If .Value >= 189.5 Then
Selection.ShapeRange.Fill.ForeColor.RGB = vbGreen
ElseIf .Value <= 185 And .Value >= 189.4 Then
Selection.ShapeRange.Fill.ForeColor.RGB = vbYellow
ElseIf .Value <= 185.4 Then
Selection.ShapeRange.Fill.ForeColor.RGB = vbRed
End If
.Select
End With
End If
If Not Intersect(Target, Range("B6")) Is Nothing Then
Me.Shapes("Oval 2").Select
With Range("B6")
If .Value >= 49.5 Then
Selection.ShapeRange.Fill.ForeColor.RGB = vbGreen
ElseIf .Value < 49.5 Then
Selection.ShapeRange.Fill.ForeColor.RGB = vbRed
End If
.Select
End With
End If
If Not Intersect(Target, Range("B7")) Is Nothing Then
Me.Shapes("Oval 3").Select
With Range("B7")
If .Value > 5 Then
Selection.ShapeRange.Fill.ForeColor.RGB = vbGreen
ElseIf .Value <5
Selection.ShapeRange.Fill.ForeColor.RGB = vbRed
End If
.Select
End With
End If
End Sub
Any thoughts in fixing this macro would be greatly appreciated. Thank you!