VBA - How to select/change shapes with the same name

[Elolo]

Hi, I developed a macro to change shapes colors based on the value of a cell. Originally I wrote this code

ActiveSheet.Shapes.Range(Array("Col")).Select
If Range("D69") = "0" Then
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(127, 127, 127)
ElseIf Range("D69") = "1" Then
Selection.ShapeRange.Fill.ForeColor.RGB = vbRed
ElseIf Range("D69") = "2" Then
Selection.ShapeRange.Fill.ForeColor.RGB = vbYellow
End If

It was working well until I realized it was not changing the format of all shapes with the same name (it changes only one of them)
Doing a little of researching in blogs I think that the looping function can address what i am looking for.
I tried again with the following code (see changes in red).

For Each Shape In ActiveSheet.Shapes
If Shape.Name Like "Col" Then
ActiveSheet.Shapes.Range(Array("Col")).Select
If Range("D69") = "0" Then
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(127, 127, 127)
ElseIf Range("D69") = "1" Then
Selection.ShapeRange.Fill.ForeColor.RGB = vbRed
ElseIf Range("D69") = "2" Then
Selection.ShapeRange.Fill.ForeColor.RGB = vbYellow
End If
End If
Next Shape

But still not changing the format of the second shape with the same name.


Would somebody please provide advice / recommendations?

Thanks!

 

[yky]

Re: VBA - How to select/change shapes with the same name (HELP PLEASE!!)

Try if this works:

[COLOR=#ff0000]For Each Shape In ActiveSheet.Shapes
If Shape.Name Like "Col" Then[/COLOR]
[COLOR=#008000]'ActiveSheet.Shapes.Range(Array("Col")).Select[/COLOR]
If Range("D69") = "0" Then
Shape.Fill.ForeColor.RGB = RGB(127, 127, 127)
ElseIf Range("D69") = "1" Then
Shape.Fill.ForeColor.RGB = vbRed
ElseIf Range("D69") = "2" Then
Shape.Fill.ForeColor.RGB = vbYellow
[COLOR=#ff0000]End If[/COLOR]
End If
[COLOR=#ff0000]Next Shape[/COLOR]

 

[Elolo]

Re: VBA - How to select/change shapes with the same name (HELP PLEASE!!)

Thanks you very much! It has work! Even with a smaller code!

I have now another issue that I could not resolve.
I am using the same logic to link the 'text' of shapes with same name to another cell. I could it make it work but I want to have it in % format (as it is in the cell). I've try different suggestions that I found in blogs but I could not make it work. See below the code I'am using.

For Each Shape In ActiveSheet.Shapes
If Shape.Name Like "TextCol" Then
Shape.TextFrame.Characters.Text = Range("F69")
Shape.TextFrame.TextRange.Characters.NumberFormat = ("0.00%")
End If
Next Shape

Would you help me with this one as well?

 

[yky]

Re: VBA - How to select/change shapes with the same name (HELP PLEASE!!)

What exactly is in F69?

 

[Elolo]

Re: VBA - How to select/change shapes with the same name (HELP PLEASE!!)

Is it a % number (lets say -15%). With the original code, is its bringing to me shape the number -0.15215

 

[yky]

Re: VBA - How to select/change shapes with the same name (HELP PLEASE!!)

Try the following code.

Dim st as String

For Each Shape In ActiveSheet.Shapes
If Shape.Name Like "TextCol" Then
st = Left(Range("F69"), InStr(Range("F69"), ".") + 2) & "%"
shape.TextFrame.Characters.Text = st
End If
Next Shape

 

[Elolo]

Re: VBA - How to select/change shapes with the same name (HELP PLEASE!!)

Thanks!!