Bdtyktl

Is it possible to format the colours of a bar chart with a single series based on their values, i.e. anything over 50 would be green, anything below would be red?

What I currently have to do is a workaround:

I have my data for the original series, and then I create 2 subsets. One for red, one for green,
and use an if statement to pull through only values above 50 for the green series, and below 50 for red series, then plot both the series, obviously where one has gaps, the other doesn't.

But this is annoying, I would much prefer for an easy way to set a conditional format on the actual series itself.

Is this possible?

Enter following VBA code as Module:

Sub FormatChartbyColour()



Set rRng = Selection





ColorIndex1 = 3

ColorIndex2 = 4







ActiveSheet.Shapes.AddChart.Select

ActiveChart.SetSourceData Source:=rRng

ActiveChart.ChartType = xlColumnClustered

ActiveChart.SetElement (msoElementChartTitleAboveChart)





ActiveChart.SeriesCollection.NewSeries

ActiveChart.SeriesCollection(3).Name = "=""Colour 1"""

ActiveChart.SeriesCollection(2).Name = "=""Colour 2"""





ActiveChart.SeriesCollection(3).Format.Fill.ForeColor.RGB = ThisWorkbook.Colors(ColorIndex1)

ActiveChart.SeriesCollection(2).Format.Fill.ForeColor.RGB = ThisWorkbook.Colors(ColorIndex2)



With ActiveChart.Parent

    .Height = 250

    .Width = 250

    .Top = 100

    .Left = 100

End With



ActiveChart.Axes(xlCategory).TickLabels.Font.Size = 10

ActiveChart.Axes(xlCategory).TickLabels.Font.Bold = False

ActiveChart.Axes(xlCategory).TickLabels.Font.Italic = True

ActiveChart.ChartTitle.Text = "Chaert Title (Colour1:Red , Colour2:Green)"

ActiveChart.ChartTitle.Font.Size = 10





With ActiveChart

    vX = .SeriesCollection(1).XValues

    vY = .SeriesCollection(1).Values





    For thisvY = 1 To UBound(vY)

        If vY(thisvY) = 1 Then .SeriesCollection(2).Points(thisvY).Interior.ColorIndex = ColorIndex1

        If vY(thisvY) = 2 Then .SeriesCollection(2).Points(thisvY).Interior.ColorIndex = ColorIndex2



    Next thisvY



End With





End Sub

How it works:

• Select Data Range A23:C27.


• RUN the Macro.

Note:

• 
  Col Index is Helper Column, you may alter Colors.


• Chart Type & Size are editable.


• Adjust cell references as you need.