Conditional Formatting in Chart

[karma]

Hello,

I have the following code, attempting to change the color of a data point based on a condition. If a certain value in a "named range" (used as source data for the chart) is 2 then the data point should look "green" on the graph.

Sub Chrt()
    Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("Sheet1!tbl"), PlotBy:=xlRows
    ActiveChart.SeriesCollection(1).XValues = "=Dyn.xls!quest"
    ActiveChart.SeriesCollection(2).XValues = "=Dyn.xls!quest"
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet2"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = Sheets("sheet1").Cells(2, 7) '"dd"
        .Axes(xlCategory).HasTitle = True
        .Axes(xlCategory).AxisTitle.Characters.Text = "Keys"
        .SeriesCollection(1).Points
        '.Axes(xlSeries).HasTitle = False
        '.Axes(xlValue).HasTitle = False
    End With
    
    Dim cell As Range
    
    For Each cell In Range("tbl") '[I]the named range[/I]
        If cell.Value = 1 Then   '[I]the condition[/I]
        ActiveChart.SeriesCollection(1).Point(14).Select ' getting an error here
            With Selection
             .Interior.ColorIndex = 43
            End With
        End If
    Next cell
    
End Sub

Any and all the help will be much much appreciated.

 

[karma]

Anyone?

 

[Jon Peltier]

This way is easier:

http://peltiertech.com/Excel/Charts/ConditionalChart1.html

 

[karma]

Hi Jon!

First of all, thanks a lot for all the help you have made available on your site! I did consult your site before I posted the problem here but have not been able to suit all the help this specific problem.

If I do use the approach on your website, I'm afraid, the size of the sheet might increase (plenty of columns will get added).

I am trying to use the source data of the chart & not the data in the sheet..I need to conditionally change the bar colors of the chart based on the data source values they contain.. e.g. if value is 1 the color of bar will be red, if 2 then green and so on.

Is this possible? Once again, thank you for the wonderful website and help!

 

[Jon Peltier]

Karma -

It's certainly possible to format the plotted points using VBA that queries some information about the data (Y values, Categories, Series Names). In fact, I did post a set of blog articles which I'd forgotten about until just now:

http://peltiertech.com/WordPress/vba-conditional-formatting-of-charts-by-value/
http://peltiertech.com/WordPress/vba-conditional-formatting-of-charts-by-category-label/
http://peltiertech.com/WordPress/vba-conditional-formatting-of-charts-by-series-name/

In general you shouldn't be afraid of adding rows and columns to make your job easier. The days when we had to conserve every byte are long gone. If you are concerned with neatness, put the calculated data onto a second sheet, and put your chart next to the original data on the first sheet. Since everything is linked, changing the original data will cause the chart to update.

Usually if you format a range to look good in a printed report (or on screen) you have to sacrifice the utility of the range for charting. You should allow yourself to have several data ranges, all linked to a primary source but each optimized for its own purpose.

 

[karma]

Thanks a ton Jon! Sorry for the late response.. have been travelling. I've used your tips & code on a temporary file and everything works perfect! Once again, thank you for being such great help! Will be around to bother again!