change shape colour based on cell value

[sainil]

I have some shapes like pupms, compressors etc on an excel sheet. How the colour of these shapes can be changed either red or greeen based on the cell values in another sheet of the same work book. If the cell value is +1 need green colour and and if -1 need red colour. I have excel 2013.

 

[sainil]

one more request. If the mapping is in sheet 1 and the linked cells are in sheet 2, how the code will change? Cell A1 to A100 in sheet 2

 

[Momentman]

On what sheet do you have the shapes? Sheet1 or 2?

 

[sainil]

I would like to have the shapes in sheet 1 and the cells linked are sheet 2.

thanks

 

[Momentman]

Right click Sheet2 Tab, select view code and paste this code in there

Private Sub Worksheet_Calculate()
    Dim shp As Shape, I As Integer
    For I = 1 To 100
        With Sheet1.Shapes.Range(Array("Group " & I))
            Select Case Range("A" & I).Value
                 Case -1
                     .Fill.ForeColor.RGB = RGB(255, 0, 0)
                 Case 1
                     .Fill.ForeColor.RGB = RGB(0, 255, 0)
                 Case Else
                    .Fill.ForeColor.RGB = RGB(255, 255, 255)
             End Select
         End With
     Next I
End Sub

 

[sainil]

thanks, it works nicely.

 

[sainil]

While renaming the shapes, I am not able to change some name. For eg. Group 43, Group 48, Group 54 and Group 57, I am not able to privde these names. I could change the names upto Group 42 without any problem. What could be the problem?. I changed the name by pressing ctrl key select the shape and edit the name in shape name box. I confirmed that there is no other shapes with these names on the work sheet.

 

[Momentman]

Can you run this code and tell me what it displays, if it displays anything at all

Sub disp_position()
    MsgBox ActiveSheet.Shapes("Group 43").TopLeftCell.Address
End Sub

 

[sainil]

Unfortunately there is no result. After editing the shape name and pressing "enter" button, the cell selection jump into the neighbouring cell and the shape name remains the same. Tried for one more shape (Group 48) after changing the code accordingly. But same result. Surprisingly other shape names like Group 44, 47,53,56 all accepts even without VBA code.

Please look into it

 

[Momentman]

Please look into it

I wish I could but I can't recreate the problem 'cos on my test sheet all the names you cited above are legal and accepted, thats of course if there's no other shape with the new name

The reason i asked you to run that code was to see if a shape with that name was somewhere on the sheet(maybe out of your view) because thats the only reason such a name would not be accepted as the new name.

you can try the code and change the name "Group 43" to one of the ones that isnt working and see if it returns any address, so we know that thats the problem

 

[sainil]

Now the message is more clear. I ran the code with "group 43, 48, and 57". The message appeared is
"Run-time error '5'
Invalid procedure call or argument"
And when I ran the code with "Group 54" the message appeared is
"The item with specified name was not found"