Thanks Vacations' Over.

I'll look into your ideas and try to get it to work.

Cheers

RodP

"Vacation's Over" wrote:

Much closer:
(((((((using macro record to get name shown on a point
sample from helpCategory Names Property

Charts("Chart1").Axes(xlCategory).CategoryNames = _
Array ("1985", "1986", "1987", "1988", "1989")
)))))))))

what you need to do is to cycle through the series names looking for
"Safeway" and if you do not find it then cycle through
Charts("yourchart").Axes(xlCategory).CategoryNames

in your series "cycle" you used the series OBJECT =name property. here the
property CategoryNames returns an ARRAY so the mechanics are different.

((((Also from Macro Recorder to set color for point:
ActiveChart.SeriesCollection(1).Points(1). .ColorIndex = 3
)))))

cycle through each series in series collection
change color for the point that has the same index as the collection name
has in the array (I would switch all the Option Base1)

This is the how and it will work. If you need more help just let me know.

PS: this could come in handy so When it works how about posting back with
code:)

"RodP" wrote:

Hiya,

I'll try and explain from the beginning again - but basically I'm wanting to
control point colour...

-I want to create a macro which formats the currently selected chart in a
set way.

Tesco = Blue
Asda = Green etc.

-There are a number of charts of varying types that we use within the
business including Column and Line charts
-Normally the data will look like this

Multiples sales '04 sale '05
Tesco 23 34
Sainsbury 34 56
Asda 56 56

-There is a need to plot the data 'by column' and 'by row'. When creating a
chart through the chart wizard button, after selecting the type of chart (eg
column (bar) chart) the wizard asks whether the series are in columns or
rows. This is the 'by column' / 'by row' thing I have been talking about in
previous emails.

- Selecting 'by rows' gives you the following x/y layout in a column chart

Sales value ¦ A S A
(y axis) ¦ S A T S A
¦ T S A T S A
¦ T S A T S A
-------¦------------------¦--------
sales '04 sales '05

sales (x axis)

Series legend: T = Tesco, S = Sainsbury, A = Asda

-Using my code - I am able to cycle through the names of the series (s.name)
easily and if s.name matches one of the cases (eg Tesco, Asda etc.), it
changes the colour of the bar accordingly.

- Selecting 'by column' gives you the following x/y layout in a column chart

Sales value ¦ B A B
(y axis) ¦ B A B A B
¦ A B A B A B
¦ A B A B A B
-------¦-------- --¦-------------¦--
Tesco Sainsbury Asda

(x axis)

Series legend: A = Sales '04, B = Sales '05

Using the code If you cycle through s.name now it now comes up with Sales
'04 and Sales '05 which is no good. And so I have written additional code
that cycles through each point of the series telling me (at the moment) the
colour of each point for each series. What I want though is not the colour
value but the x value (ie. the category value). Incidentally, when you place
the mouse over each point or the chart, a little box pops up displaying the
value of the point aswell as the x (or category) value - this is what I want
to get hold of through VB.

This is how i'm cycling through each point of each series at the moment:

Sub cyclethrupoints()
Dim pt As Point
Dim s As series

With ActiveChart
For Each s In .SeriesCollection
For Each pt In s.Points
pt.Select
MsgBox pt.Interior.Color
Next
Next
End With

End Sub


Hope this makes things a little more clearer in terms of what I am trying to
do and I hope you can help further.

Many thanks

RodP