2 questions on color coding objects

[S R Tipton]

Can anyone help me with 2 Excel problems?

1. If I insert an autoshape from the drawing toolbar, how can I have
the fill color of the autoshape change based on criteria (i.e. if
<85%, fill with red, 85 to 95%, fill with yellow, >95% fill with
green).

2. I have a clustered column bar chart; I would like EACH bar (not
each series) to change color based on criteria (as above).

Thanks for any help or advice.

 

[JulieD]

Hi

i can help with Q1 (although there might be a better solution lurking out
there!)

in the code module for the worksheet that your drawing object lives on use
the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

if Range("B2") >= 95 then
Worksheets(1).Shapes(1).Fill.ForeColor.RGB = RGB(0, 255, 0)
elseif Range("B2") >= 85 then
Worksheets(1).Shapes(1).Fill.ForeColor.RGB = RGB(0, 0, 255)
elseIf Range("B2") < 85 Then
Worksheets(1).Shapes(1).Fill.ForeColor.RGB = RGB(255, 0, 0)
End If

End Sub

where B2 holds the formula that calculates to the appropraite %
this will make the object green / blue or red depending on the criteria
(don't know the RGB for yellow off the top of my head)

Hope this helps
Cheers
JulieD

 

[Frank Kabel]

Hi
for the second question have a look at
http://peltiertech.com/Excel/Charts/format.html

 

[S R Tipton]

Thanks folks! You help is appreciated!

Steve