Conditional Formatting Drawing Objects

[Jon Rathbun]

How to format the color of a drawing object based on
data "connected" to it - to create a Red/Yellow/Green
style flowchart that changes from month to month depending
on process performance?

I can conditional format CELLS in my sleep - but would
like to conditionally format drawing objects...

Any ideas?

 

[Peter Atherton]

Jon

This should give you some ideas. Assign the auto-shapes
to the (edited) macro.

Sub colorShape()
Application.OnCalculate = "Worksheet_Calculate"
End Sub
Private Sub Worksheet_Calculate()
If range("B3") >= 50 Then
ActiveSheet.Shapes("AutoShape 1").Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 43
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Else
ActiveSheet.Shapes("AutoShape 1").Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 35
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
End If
range("B1").Select
End Sub

Regards
Peter