Conditional Colour Bars in Chart 1

[Igwiz]

Having been referred from another forum, can someone give me a clue on a VBA way of doing this. I am aware of the spreadsheet workarounds but want to do it with code. Thanks.

------
Hi all,

On a bar chart, I would like to loop through each of the data points and if the value is say <10, then change the colour of the bar to say red. I know how to change the colour in vba by looping through SeriesCollection(1).Points but how do I test the data value it is associated with.
i.e. what is the property associated with Point that returns the datavalue

Many thanks.

Ig

 

[SkipVought]

Hi,

Here's a VBA solution to your problem. It assumes
1. that the data range of values has a Range Name of Value
2. that there is one chart on the sheet
3. that there is one series...

Sub ChangeColors()
    With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
        i = 1
        For Each p In .Points
            If Range("Value")(i) > 10 Then
                p.Interior.ColorIndex = 3
            Else
                p.Interior.ColorIndex = 4
            End If
            i = i + 1
        Next
    End With
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Igwiz]

Brilliant Skip - many thanks for your help!

 

[Stretchwickster]

Igwiz, seeing as Skip has resolved your problem, it would be worth rewarding him with a star otherwise he may become disheartened ;-)

You can do this by clicking the &quot;Mark this post as a helpful/expert post!&quot; just after Skip's post.

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)