Conditional Formatting Shapes that contains specific words

[liberovago]

Hi to all!

My question regards a vba code.
How to conditional Formatting Shapes in a sheet?

I have a product list on sheet1
Many shapes on sheet 2, linked to every cell in sheet1 that contains that product name.
I need to color the background of every shape, if they contains the word for example "BETA".

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub liberovago()
   Dim Shp As Shape
   
   For Each Shp In Sheets("Sheet2").Shapes
      If InStr(1, Shp.TextFrame.Characters.Caption, "Beta", vbTextCompare) > 0 Then Shp.Fill.ForeColor.RGB = 45678
   Next Shp
End Sub

 

[liberovago]

Hi & welcome to MrExcel.
How about

Sub liberovago()
   Dim Shp As Shape
  
   For Each Shp In Sheets("Sheet2").Shapes
      If InStr(1, Shp.TextFrame.Characters.Caption, "Beta", vbTextCompare) > 0 Then Shp.Fill.ForeColor.RGB = 45678
   Next Shp
End Sub

Thanks for the reply!

Unfortunately seems not functioning...

Runtime error 9. I've changed the name of the sheet (in italian "Foglio4" and the word that I'm looking for "Finocchi". Maybe it's just me and some mistake

 

[Fluff]

You are trying to use the sheet codename, rather than the actual sheet name, so it should be

For Each Shp In Foglio4.Shapes

 

[liberovago]

You are trying to use the sheet codename, rather than the actual sheet name, so it should be

For Each Shp In Foglio4.Shapes

Yes, that's my error. But now the error is 1004.

On another excel (test) file, your code is perfect.

 

[Fluff]

What is the actual error message?

 

[liberovago]

"valore specificato fuori dai valori previsti" means almost "specified value outside expected values"

 

[liberovago]

Maybe I've found something... In the sheet of the shapes, I've some shapes grouped. I've tried to ungrouped the shapes and the code works! Many thanks!!!

 

[liberovago]

Maybe I've found something... In the sheet of the shapes, I've some shapes grouped. I've tried to ungrouped the shapes and the code works! Many thanks!!!

But now I've another problem.. In this sheet I've also some little images, and a couple of 3d model.. How can I exclude these objects?

 

[Fluff]

Ok, how about

Sub liberovago()
   Dim Shp As Shape
   
   For Each Shp In Foglio4.Shapes
      If Len(Shp.OLEFormat.Object.Formula) > 0 Then
         If InStr(1, Shp.TextFrame.Characters.Caption, "Finocchi", vbTextCompare) > 0 Then Shp.Fill.ForeColor.RGB = 45678
      End If
   Next Shp
End Sub