Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Using DIM

  Asked By: Nichole    Date: Feb 15    Category: MS Office    Views: 682
  

I am trying to change the colours of an bar within a Excel chart.
Whilst I can record a macro and edit it so that it changes different
bars (points) of the graph I am having difficulty in writing VBA to
change the points value.

An extract of the code I am using to change the colour is:

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(4).Select
ActiveChart.SeriesCollection(4).Points(5).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 51
.Pattern = xlSolid
End With

I really need the points(5) number to be driven by a reference on the
worksheet?

ANy ideas would be really appreciated.

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Jawahir Burki     Answered On: Feb 15

Here is what I have used. It looks at the values of the data in the
chart and changes the colors accordingly. If you send me an email,
I'll send you a demo worksheet.

Place this in the code  page for the sheet where that chart(s) are:

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
Next
Next
End Sub

Function ColorScheme(v)
If v < 0.25 Then
ColorScheme = 46 'Orange
ElseIf v <= 0.5 Then
ColorScheme = 3 'Red
Else
ColorScheme = 4 'green
End If
End Function

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




Tagged:  

 
 
 
 

Related Post