Using variable in Worksheets.Shapes.Range(Array("")).Select

[jlc19]

I am trying to build a dashboard in Excel using shapes that will change the foreground color based on a value. I have two sheets, Performance Data and Integrated Design. Right now I have 2 shapes that I would like to have change color bed on the values in Performance Data Cells G2 and G3; based on if the text in the cell is Red Yellow or Green. I gave a For Next Loop that will loop through the cells just fine, but I cannot seem to use a variable in the Worksheets.Shapes.Range(Array("")).Select.

The loop works fine if I hardwire the shape name in the Worksheets.Shapes.Range(Array("")).Select lines, it goes through the loop and changes color based on cell G3.

I tried naming the shapes 2 and 3 but the DC in the Worksheets.Shapes.Range(Array("")).Select causes an error.

Is there a way to assign a variable and use that variable to loop through a set of shape names in a for next loop?


Here is my code that errors out:

Private Sub Worksheet_Change(ByVal Target As Range)


Dim PD As Variant
Dim i As Variant
Dim DC As String


For i = 2 To 3
PD = Worksheets("Performance Data").Range("G" & i)
DC = i

If PD = "Red" Then
Worksheets("Integrated Design").Shapes.Range(Array("DC")).Select
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 0, 0)
Else
If PD = "Green" Then
Worksheets("Integrated Design").Shapes.Range(Array("DC")).Select
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(0, 255, 0)
Else
If PD = "Yellow" Then
Worksheets("Integrated Design").Shapes.Range(Array("DC")).Select
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 255, 0)
End If
End If
End If

Next i

Range("A1").Select

End Sub

 

[DanteAmor]

Hi and welcome to MrExcel

Could you explain in more detail what you mean by:

I tried naming the shapes 2 and 3 but the DC in the Worksheets.Shapes.Range(Array("")).Select causes an error.

That is, each shape has a name, what those names are, what the name says and what you expect as the result.

 

[Tetra201]

See if the following sub works for you:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Long
    Dim FillColor As Long
    For i = 2 To 3
        Select Case Worksheets("Performance Data").Range("G" & i)
            Case "Red": FillColor = RGB(255, 0, 0)
            Case "Green": FillColor = RGB(0, 255, 0)
            Case "Yellow": FillColor = RGB(255, 255, 0)
        End Select
        Worksheets("Integrated Design").Shapes(CStr(i)).Fill.ForeColor.RGB = FillColor
    Next i
    Range("A1").Select
End Sub

 

[jlc19]

Hi and welcome to MrExcel

Could you explain in more detail what you mean by:


That is, each shape has a name, what those names are, what the name says and what you expect as the result.

So for my prototype I have 2shapes. I have tried 2 naming conventions:

1. Descriptive names to make it easy to identify which shape is which: LOO1DC2 and LOO1DC3. The numbers are the only changes in the names to differentiate the shapes.
2. Simple numeric integers: 2 and 3

What I am trying to do is to create a loop where the value in G2 on worksheet Performance Data is checked and shape LOO1DC2 changes it's foreground color based on the value; and then he value in G3 on worksheet Performance Data is checked and shape LOO1DC3 changes it's foreground color based on the value.

I want to use a loop to avoid having to add a separate routine for each shape. The code works fine to check the cells since is I replace "DC" with LOO1DC2 it changes that shape's color based on cell G3 since that is the last value it checks in the loop.

Thanks
Jim

 

[jlc19]

See if the following sub works for you:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Long
    Dim FillColor As Long
    For i = 2 To 3
        Select Case Worksheets("Performance Data").Range("G" & i)
            Case "Red": FillColor = RGB(255, 0, 0)
            Case "Green": FillColor = RGB(0, 255, 0)
            Case "Yellow": FillColor = RGB(255, 255, 0)
        End Select
        Worksheets("Integrated Design").Shapes(CStr(i)).Fill.ForeColor.RGB = FillColor
    Next i
    Range("A1").Select
End Sub

Thanks. It works great.

So I understand logic:

Select Case gets the value in the referenced cell G2, G3 , etc. as it loops and sets it as the Case
Case selects the color to use based on the value returned by Case and set the variable FillColor to the value
CStr (i) converts I to a string to properly identify the shape
Worksheets then colors the shape

Thanks for helping me learn a new function called Case. I can see where it would be useful.

I modified teh code a bit so I could name the shapes descriptively

LOO refers to which row the shape is in and is used so as I add rows I can change it from LOO1DC to LOO2DC etc in a loop
The Range("A1").Select is just to move the active cell to a hidden position behind a shape so if teh client selects a cell by accident it unhighlights it.

Now I just need to add a code to run this code whenever that sheet is activated so it auto updates.

Here's my working code thanks to the two of you:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
Dim FillColor As Long
Dim ShapeName As String
Dim LOO As String

LOO = "LOO1DC"

For i = 2 To 5
Select Case Worksheets("Performance Data").Range("G" & i)
Case "Red": FillColor = RGB(255, 0, 0)
Case "Green": FillColor = RGB(0, 255, 0)
Case "Yellow": FillColor = RGB(255, 255, 0)
End Select
ShapeName = LOO & i
Worksheets("Integrated Design").Shapes(ShapeName).Fill.ForeColor.RGB = FillColor
Next i
Range("A1").Select
End Sub