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

J

jlc19

New Member
Joined
May 14, 2023
Messages
3
Office Version
  1. 365
Platform
  1. MacOS
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Hi and welcome to MrExcel

Could you explain in more detail what you mean by:
jlc19 said:
I tried naming the shapes 2 and 3 but the DC in the Worksheets.Shapes.Range(Array("")).Select causes an error.
Click to expand...

That is, each shape has a name, what those names are, what the name says and what you expect as the result.
 
Upvote 0
See if the following sub works for you:
VBA Code: 
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
 
Upvote 0
Solution
DanteAmor said:
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.
Click to expand...

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
 
Upvote 0
Tetra201 said:
See if the following sub works for you:
VBA Code: 
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
Click to expand...

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
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Deleting all shapes in all selected cells
Replies
1
Views
122
Mesh
M
I
  • Question
Error in using VBA Macro with Apply Colors and Line Numbers
Replies
3
Views
157
intellitechdataservices
I
J
  • Solved
Excel VBA - Color the shapes when shape is selected
Replies
5
Views
802
jalmbris
J
Deladier
  • Question
Deselect Freeform after Macro has ran (VBA)
Replies
3
Views
114
Domenic
D
A
  • Question
Excel VB Organogram
Replies
1
Views
153
anuradhagrewal
A

Forum statistics

Threads
1,216,500
Messages
6,131,015
Members
449,615
Latest member
Nic0la

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top