r/vba•
Animosity16

Reference shapes by name located in a cell

Solved

So I am trying to get the a shape to change color based on values of cells. Easily done for single shapes if you specify actual name.

The shapes have the same name as the column headers above data cells. So I want the code to loop through each data cell and change the shape for that data point using the column header name, which is same for the shape name.

So far this is what I got.

Dim i as long
Dim dataws as worksheet
Dim imagews as worksheet

Dataws=worksheets ("data")
Imagews=worksheets ("image")

For i = 2 to colcount 
Colcount = '.... Can't remember exactly what I use but it works)
If dataws.cells(2,i).value > 2 then
  Imagews.shapes(dataws.cells(1,i).value).fill.forecolor.rgb=vbRed

End if
Next I

It seems to work great going through the data cells. But it doesn't let me reference the shape names by the cell text.

Any help would be greatly appreciated!

Posted Sep 12, 2019, 3:16 PM UTC

121
Loading comments…