Colour a bar chart depending on sourcedata value 1

[waynerenaud]

I need to automatically color a point on a bar chart if the source data value is <60.
I cannot find how to get the point.value
I can get the point.datalabel.text but then cannot match it to a value.
something like:
if dp.value <60 then
dp.interior.colorindex = 3
end if
Please help.

 

[waynerenaud]

Ok Guys - help me out here...........
Is there no response becuase it cannot be done, or do I need to word the question better ??
Please help

 

[SkipVought]

Hi,

Easy - and no VBA needed.

You set up new data ranges with formulas to display the vaues you want. Assuming that you have a column of data in column B that you are plotting,

in column C
[tt]
=if(B2<60,B2,0)
[/tt]
in column D
[tt]
=if(B2<60,0,B2)
[/tt]
Then plot columns C & D instead of B. The column C series is one color and the column D another.

VOLA!

Skip,

http://www.TheOfficeExperts.com

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

 

[waynerenaud]

Hey Skip, thanks for the suggestion. There was me looking for a really complex vba solution, and just taking a different approach seems so simple, but it alluded me for hours. I will give it a try this afternoon.
Thanks again.

 

[waynerenaud]

Skip, it worked great so a star for a quick simple answer.....however, purely for learning purposes, can anyone tell me how I could do this in vba. Thanks

 

[SkipVought]

    With ActiveSheet.ChartObjects(1).Chart
        j = 0
        For Each s In .SeriesCollection
            For Each dp In s.Points
                j = j + 1
                If s.Values()(j) > 50 Then
                    dp.Interior.ColorIndex = 3
                Else
                    dp.Interior.ColorIndex = 6
                End If
            Next
        Next
    End With

Skip,

http://www.TheOfficeExperts.com

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