Changing shape and font colour based on text

Hello people!

I have some shapes with text linked to certain cells. When the cell value is "R", I want the shape colour and the text inside the shape colour to be red. The same with B for blue and G for green. I have attached an example. I need a script that runs through the shapes in the sheet and changes the shape and font colour accordingly. I have tried a few times, but couldn't come up with a solution.

When I try to Dim Shp As Shape, I get an error at the For Each Shp in Sheets("...").Shapes . It seems that no shape is found.

Can anyone help me with a solution?

Thank you
!

This code seems to loop through all you Shapes in the worksheet and select them.

Please Login or Register  to view this content.

re your not working code try to add after the line "For Each ....

Please Login or Register  to view this content.

Alf

I guess you were wanting to hide the text in the shapes? If so, this will work. Put it before the last End Sub in Olly's post #3.

Please Login or Register  to view this content.

Thanks, guys!

I've tried Olly's approach and I get a "Method 'Range' of object '_Worksheet' failed" error. at this line:

Please Login or Register  to view this content.

I can't really read through the logic of your code Olly, so can you help me fix this error? It colours one triangle and then I get the error.

Thanks a lot!

I don't need to have the objects grouped. It's not necessary. I just did that to keep the triangles all together, but I can ungroup them if that helps.

Thanks Olly! It works.

There's one minor tweak I need to do. I will add some textboxes as well, so I would need it to skip those shape types (type 17, I believe) and only colour the shape and text of the triangles (type 5, I think). How do I insert that condition into the logic?

Cheers!