Chnage colour of shape when certtain criteria are met

[RobSA]

Hi Folks,


I am wondering the following.


If I have inserted a shape of colour red, can I get the colour to change if data automatically satisfy certain cirteria to say blue.


Regards rob

 

[Meedan]

You can use macro to do this. I use this in one of my current sheet. Just to help you with the code.


If (Range.Value) = "(Criteria)" Then

Sheets("Sheetname").Shapes("Shapename").Select

Selection.ShapeRange.Fill.ForeColor.RGB = 2764187

 

[Luke M]

To elaborate on Meedan's code, if you want it to change dynamically (like conditional formatting), you could copy this code to the sheet module (tweaked to suit your liking)

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
'Which cell controls the color?
If Intersect(Target, Range("A2")) Is Nothing Then Exit Sub
If Target.Value = 2 Then
Sheets("Sheetname").Shapes("Shapename").Fill.ForeColor.RGB = 2764187
ElseIf Target.Value = 3 Then
Sheets("Sheetname").Shapes("Shapename").Fill.ForeColor.RGB = 1254613
'Keep copying the ElseIf for more criteria...etc
Else
'If you have a "default" color
Sheets("Sheetname").Shapes("Shapename").Fill.ForeColor.RGB = 1000000
End If
End Sub

[/pre]

 

[Andrew]

any chance you could provide a workbook with an example of this in, as I can't seem to make it work?


thanks

 

[Hui]

Andrew

Have a look at:

https://rapidshare.com/files/1797758079/Color_Shape.xlsm


It has basic instructions in the file

Select the shape and take note of its name in the Name Box