My First VBA attempt (Dashboard with Arrows and Ranges)

[neokeelo]

Hi. I am trying to make a simple Dashboard that will place an arrow in a Green Yellow or Red area based on a value I enter into a certain cell.

The arrows for the dashboard are all located behind it. I just need to bring the specific one to the front and hide the other ones. For now to keep it simple I just want to use 1 arrow in each zone. (3 total)

I am a beginner using VBA and I don't understand how to define my variables or which commands I should use. Could you take a look at my program and fix it for me? Thank you.

Here is my really crappy code with my comments ( Sorry I know nothing about VBA coding)

[davegugg]

Sub Dashbaord()
'define all the things I need to use in the program
Dim Score As Integer

'These are the three ranges that deterimine where the arrow will be but i dont how do you define a range?
Dim RangeGreen As Range
Dim RangeYellow As Range
Dim RangeRed As Range

'Keep all the definitions on top
GreenArrow As Object
YellowArrow As Object
RedArrow As Object

'This defines the range
Set RangeGreen = Range("AI7:AJ13")
Set RangeYellow = Range("AI14:AJ19")
Set RangeRed = Range("AI20:AJ25")

' If the value entered in cell AH33 is between 0 to 26 select the green arrow and bring it to the front
' and put the other arrow in the back.
' if the value is between 27 and 51 bring the yellow arrow to the front ect...

'This Section needs some changes!!!
If Range("AH33") = RangeGreen Then
    ActiveSheet.Shapes(GreenArrow).Select
    Selection.ShapeRange.ZOrder msoBringToFront
ElseIf Range("AH33") = RangeYellow Then
    ActiveSheet.Shapes(YellowArrow).Select
    Selection.ShapeRange.ZOrder msoBringToFront
ElseIf Range("AH33") = RangeRed Then
    ActiveSheet.Shapes(RedArrow).Select
    Selection.ShapeRange.ZOrder msoBringToFront
End If

End Sub

Here is an update for you, however, this code is not correct. You can see you set your green, red, and yellow ranges. However, toward the end you say if AH33 = RangeColor. A range can't be equal to another range. That is like saying if New York = Chicago. You need to look at a specific aspect of the range, usually the value. So you can say:

If Range("AH33").Value = RangeGreen.Value Then

However, that won't quite work as RangeGreen is a number of cells, and thus has multiple values. So you're going to need to decide what you are comparing AH33 to, and continue from there. My guess is you'll need to define the greatest and least values in each colored zone, then check the Input to see which zone it lies in. Good Luck, and feel free to ask any questions that come up.