MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

chart conditional formatting

  Asked By: Rena    Date: Nov 09    Category: MS Office    Views: 803

I am creating a very simple chart and i want the colour fill within
the bar chart to change colour depending on the value of a cell. ie
colour orange if less than 25% or colour red if between 25 and 50% or
colour green if over 50%

i have managed to find the conditional formatting which controls the
contents of the cell but how do i apply this to the chart

I am guessing it is vba or a macro

If someone could help me with this i would be extremely grateful



3 Answers Found

Answer #1    Answered By: Ethan Bouchard     Answered On: Nov 09

Here is an example that can get you started. The code below assumes
you have a value in cell  A7 and a bar  or column chart  showing that
value. Put this code in the sheet by right-clicking in the sheet's
tab and choosing "view code".

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$7" Then
Set x = ActiveSheet.ChartObjects("Chart 1") _
If Target.Value < 0.25 Then
x.ColorIndex = 46 'Orange
ElseIf Target.Value <= 0.5 Then
x.ColorIndex = 3 'Red
x.ColorIndex = 4 'green
End If
End If
End Sub

Answer #2    Answered By: Rose Hughes     Answered On: Nov 09

Only trouble now is how do I expand the code to say work with the
next cell  as in A8 and A9. I have copied the existing code and
changed the cell ref but now it changes all the columns to the same
colours based on the value of what is in cell A7

Answer #3    Answered By: Jessica Brown     Answered On: Nov 09

Here is a modification that takes the data from the chart  rather
than the sheet, so it doesn't mater where the data is. It also goes
through all charts on the sheet. I tested it for the basic bar  and
column charts only. Brad

Private Sub Worksheet_Change(ByVal Target As Range)
For Each MyChart In ActiveSheet.ChartObjects
MyVals = MyChart.Chart.SeriesCollection(1).Values
Set MyPoints = MyChart.Chart.SeriesCollection(1).Points
i = 1
For Each p In MyPoints
v = MyVals(i)
p.Interior.ColorIndex = ColorScheme(v)
i = i + 1
End Sub
Function ColorScheme(v)
If v < 0.25 Then
ColorScheme = 46 'Orange
ElseIf v <= 0.5 Then
ColorScheme = 3 'Red
ColorScheme = 4 'green
End If
End Function

Didn't find what you were looking for? Find more on chart conditional formatting Or get search suggestion and latest updates.