VBA | Select if function with shapes

[Huber]

Hi all,

I'm seeking a solution to the following problem:

There is a Swiss country map on a post code basis (hundreds of shapes) on the excel sheet in question (see image attached). There are two groups. Group 1 of post codes is colored blue. Group 2 of post codes is colored green. I want to change the color of the shapes colored green to grey.

Formulated pragmatically I imagine the following steps in VBA:

(1) Select all shapes of the worksheet colored green
(2) Change color of selected shapes to gray

My question: Does anyone know how to code this? Or of an alternative solution?

Thanks for any help!

Best,
Marc

 

[Micron]

Do this on a copy of the sheet or workbook to test.
Suggest, first find the name of one shape that you want to change. Then run this from a standard module and substitute your sheet name and the Name of that shape:

Sub shapeName()
Dim shp As Shape

For Each shp In Sheets("4").Shapes
   If shp.Name = "aNameHere" Then Debug.Print shp.Fill.BackColor
Next

End Sub

Now you know the colour that you want to change. Suppose that is 16777215. Now colour that shape as you wish and run that again. Now you know the colour you want to change to. Then in that same module have

Sub shapeColour()
Dim shp As Shape

For Each shp In Sheets("4").Shapes
     If  shp.Fill.BackColor = oldColourNumber Then shp.Fill.BackColor = NewColourNumber
Next

End Sub

 

[Huber]

Do this on a copy of the sheet or workbook to test.
Suggest, first find the name of one shape that you want to change. Then run this from a standard module and substitute your sheet name and the Name of that shape:

Sub shapeName()
Dim shp As Shape

For Each shp In Sheets("4").Shapes
   If shp.Name = "aNameHere" Then Debug.Print shp.Fill.BackColor
Next

End Sub

Now you know the colour that you want to change. Suppose that is 16777215. Now colour that shape as you wish and run that again. Now you know the colour you want to change to. Then in that same module have

Sub shapeColour()
Dim shp As Shape

For Each shp In Sheets("4").Shapes
     If  shp.Fill.BackColor = oldColourNumber Then shp.Fill.BackColor = NewColourNumber
Next

End Sub

Thank you!