Macros

I am trying to change the color of graphs depending on the values being graphed (In other words, some graphs will have yellow lines, some red, and some green.) How do I run a macro automatically based on a certain condition? Is there a way to name my macros, so I can put an IF statement in and run from there? I have tried conditional formatting, but it doesn't work with graphs (or I haven't figured it out yet.). I can get my data to change colors using this, but am stuck. Any ideas would be greatly appreciated. Thanks.

Code like this would work:

Sub Macro1()

Range("A1").select
Select Case Activecell.value

Case is < 5
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
With Selection.Interior
.ColorIndex = 5
.Pattern = xlSolid
End With
Application.CommandBars("Chart").Visible = False

Case is < 10
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
With Selection.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
Application.CommandBars("Chart").Visible = False

Case is <20
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Application.CommandBars("Chart").Visible = False

End Select

End Sub

Obviously you will need to change the cell where the macro starts adn the ranges but that's the general gist.

The easiest way I think to find out the colour indexs' is just to record a macro and change the colour of something then you will see what the colour index is when you go into VB.

Craig