VBA Selecting Shapes

[Jasesair]

I have the below code which is working quite well. I'm wanting it to only be changing the colours from a range of the shapes I have on the sheet, not them all. Any idea how to do this?

Sub SetColor(v)
  Dim cell As String
  cell = ActiveCell.Address
  ActiveSheet.Shapes.SelectAll
  Selection.ShapeRange.Fill.ForeColor.RGB = RGB(175, 171, 171)
  ActiveSheet.Shapes(v).Fill.ForeColor.RGB = RGB(224, 255, 124)
  Range(cell).Activate
End Sub

 

[Fluff]

When you say a range of shapes, do you shapes that exist in a certain range (eg A1:D100)
or do you mean shapes with certain names?

 

[Jasesair]

Unfortunately the shapes are all in a large cell A1, with other shapes also in the cell that I don't want included. I have labelled the shapes but I'm not sure if that's the same as naming them. They're just labelled from 1-10.

 

[Fluff]

OK, try

Sub SetColor(v)
   ActiveSheet.Shapes.Range(Array("Oval 2", "Oval 4", "Oval 3")).Fill.ForeColor.RGB = RGB(175, 171, 171)
   ActiveSheet.Shapes(v).Fill.ForeColor.RGB = RGB(224, 255, 124)
End Sub

Change the values in red to match the names of your shapes. To find the names click on one of the shapes & look in the name box (to the left of the formula bar).

 

[Jasesair]

Hi Fluff, thanks so much for all your help. Not sure what is the problem, but I'm getting an error on the first line. I can't see what would be wrong!

Sub SetColor(v)
    ActiveSheet.Shapes.Range(Array("Rectangle 26", "Rectangle 51", "Rectangle 52", "Rectangle 53", "Rectangle 54", "Rectangle 55", "Rectangle 56", "Rectangle 57", "Rectangle 58", "Rectangle 59")).Fill.ForeColor.RGB = RGB(175, 171, 171)
    ActiveSheet.Shapes(v).Fill.ForeColor.RGB = RGB(224, 255, 124)
End Sub

 

[Fluff]

What error do you get?

 

[Jasesair]

Well, that's all it took - a little prompt to actually look at the error message! Turns out the shapes were all 'rounded rectangles'!

This now works a treat. Many thanks for all your assistance!!!

 

[Fluff]

Glad you sorted it & thanks for the feedback.