Loop procedure to change color of 36 shapes based on cell value

[Betsy Simpkins]

Hello all! I am stuck trying to find the best way to loop the code to change each of 36 shapes based on the values in corresponding 36 cells. The only code I have been able to find is

If IsNumeric(Target.Value) Then
If Target.Value < 0.15 Then
ActiveSheet.Shapes("Freeform 45").Fill.ForeColor.RGB = RGB(235, 241, 222)
ElseIf Target.Value >= 0.15 And Target.Value <= 0.199 Then
ActiveSheet.Shapes("Freeform 45").Fill.ForeColor.RGB = RGB(216, 228, 188)
ElseIf Target.Value >= 0.2 And Target.Value < 0.249 Then
ActiveSheet.Shapes("Freeform 45").Fill.ForeColor.RGB = RGB(196, 215, 155)
Else
ActiveSheet.Shapes("Freeform 45").Fill.ForeColor.RGB = RGB(118, 147, 60)
End If

This works but requires me to somehow nest these IFs and I would have to manually change the shape names in the code. Is there way to have the code to change the color run for each cell in a range and change the color of its corresponding shape based on the value? Conditional formatting won't work I think because I have a non-standard shape.

Thanks so much in advance for any and all help!

[xladept]

With no sample I can't test it but maybe something like:

Please Login or Register  to view this content.

[Betsy Simpkins]

Thanks so much for your quick reply. But from what I can see from your code, I am not clear how I would change the OTHER 35 shapes. The name "Freeform 45" is the name for only one of the shapes. So I'm hoping to find a way to reference the CELL which contains the name of the shape...and have it somehow use the value of that cell to recognize the shape to change the color of. And then of course repeat that for each of the 36 cells that have data.

Basically the table is:

County %Pop ShapeName
Baker .25 Freeform 45
Lake .15 Freeform 17
etc
etc

Betsy

[xladept]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Betsy Simpkins]

Thank you so much! Attached is a sample of what I'm trying to do with a before and after sheet.

[xladept]

Hi Betsy,

Try this:

Please Login or Register  to view this content.

[Betsy Simpkins]

Thank you SO much! That works perfectly! Now I just need to learn more about FFS and FFC. I truly appreciate your help!
Betsy

[xladept]

You're welcome!

FFC is a variant used to hold the RGB
FFS is a String used to hold the Free Form Shape

[Betsy Simpkins]

Thanks, that does help me understand. Would this work for a picture as well? For example, I created these as shapes. But, if I used Photoshop or Illustrator to more accurately create the images and imported them into Excel as pictures, would this still work?

[xladept]

It's kind of like magic, if you have the name, you can do what you want

And, thanks for the rep!