VB for formatting excel graph colours

[johnbailey]

Hi,
I'm having a problem creating the VB code which formats the colours in a graph depending on the outcome of another cell. Or to be able to format the graph to begin with to react to values on a conditional format basis.
Would appreciate the help.
Thanks
john

 

[PHV]

Have you tried to play with the Macro recorder ?
What have you so far ?

 

[johnbailey]

PHV,
Hi, trying to use an IF statement but haven't done one altering graphs before, I think the structure is wrong. Do you think an IF ELSE would be easier?
Thanks
John


Range("L284&quot.Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=TRUE,"
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).Points(2).Select
With Selection.Interior
.ColorIndex = 5
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).Points(2).Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End Sub

 

[SkipVought]

john,

Here's an example of what can be done

Sub ColorTest()
'5 series each with 10 points
    For lRow = 1 To 5
        For iCol = 1 To 10
            With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(lRow).Points(iCol).Interior
                Select Case Cells(lRow, iCol).Value
                    Case Is < 0
                        .ColorIndex = 5
                        .Pattern = xlSolid
                    Case Is = 0
                        .ColorIndex = 7
                        .Pattern = xlSolid
                    Case Is > 0
                        .ColorIndex = 9
                        .Pattern = xlSolid
                End Select
            End With
        Next
    Next

End Sub

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884