Thread: Macros

    #1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Posts
    1
    Rep Power
    0

    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.
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Location
    England
    Posts
    2
    Rep Power
    0
    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

IMN logo majestic logo threadwatch logo seochat tools logo