I am building a workbook with a signle datasheet (Summary_Worksheet) and many
chart sheets. Only a 15 columns by 32 rows area has data for charts.

One chart is a filled radar type chart, rest are simple bar charts.

Series for radar chart are based on columns from the datasheet. Each line
(colored distinctivly) represent a column, each spike represent a row.

SourceData Series Values for radars are, for example:
=Summary_Worksheet!$K$1068:$K$1099

Series for bar charts are based on rows:

=Summary_Worksheet!$I$1102:$W$1102

Series for bar charts are built on rows with 15 bars on each, representing
15 values in the same columns, used in radar charts.

I would like to control the color of chart items, listed below by colors of
specific cells in the same column where data for a particular series are
located. I have a finite number of series (15). Looks like I need code
something like that, probably in ThisWorkbook:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

If Sh.Type = RadarChart

For i=1,15
' How do I name series?
' let's say I put colors in row 1067, columns I-W (column numbers 9-23)

' line color on the radar chart
' for i=1 Range will be ($9$1067) which is first colored cell...
' not clear how to tie Series(i) to a series based on a specific column

Series(i).Line.Color = Worksheets("Summary_Worksheet").Range($(8 +
i)$1067)).Color

'each series has a checkbox which allows to hide/unhide a column (we hide
when no data).
'each checkbox has its own name - in my case by a column it represents:
cbI40, cbK40, cbL40 etc
' row 40 is used because it contains names of series, which are used in
legend and labels:
' =Summary_Worksheet!$J$40 for example
' perhaps that is how I can name my series for radar?...

cbCheck_Box_Name.Background = Worksheets("Summary_Worksheet").Range($(8 +
i)$1067)).Color

'
Series(i).LegendKey.Color = Worksheets("Summary_Worksheet").Range($(8 +
i)$1067)).Color

End For
End If

If Sh.Type = BarChart

For i=1,15

DataPoint.Color for Series(i) = Worksheets("Summary_Worksheet").Range($(8 +
i)$1067)).Color

End For
End If

Thank you!

Am I asking too much? Excel can not do that??

"Naum" wrote:

I am building a workbook with a signle datasheet (Summary_Worksheet) and many
chart sheets. Only a 15 columns by 32 rows area has data for charts.

One chart is a filled radar type chart, rest are simple bar charts.

Series for radar chart are based on columns from the datasheet. Each line
(colored distinctivly) represent a column, each spike represent a row.

SourceData Series Values for radars are, for example:
=Summary_Worksheet!$K$1068:$K$1099

Series for bar charts are based on rows:

=Summary_Worksheet!$I$1102:$W$1102

Series for bar charts are built on rows with 15 bars on each, representing
15 values in the same columns, used in radar charts.

I would like to control the color of chart items, listed below by colors of
specific cells in the same column where data for a particular series are
located. I have a finite number of series (15). Looks like I need code
something like that, probably in ThisWorkbook:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

If Sh.Type = RadarChart

For i=1,15
' How do I name series?
' let's say I put colors in row 1067, columns I-W (column numbers 9-23)

' line color on the radar chart
' for i=1 Range will be ($9$1067) which is first colored cell...
' not clear how to tie Series(i) to a series based on a specific column

Series(i).Line.Color = Worksheets("Summary_Worksheet").Range($(8 +
i)$1067)).Color

'each series has a checkbox which allows to hide/unhide a column (we hide
when no data).
'each checkbox has its own name - in my case by a column it represents:
cbI40, cbK40, cbL40 etc
' row 40 is used because it contains names of series, which are used in
legend and labels:
' =Summary_Worksheet!$J$40 for example
' perhaps that is how I can name my series for radar?...

cbCheck_Box_Name.Background = Worksheets("Summary_Worksheet").Range($(8 +
i)$1067)).Color

'
Series(i).LegendKey.Color = Worksheets("Summary_Worksheet").Range($(8 +
i)$1067)).Color

End For
End If

If Sh.Type = BarChart

For i=1,15

DataPoint.Color for Series(i) = Worksheets("Summary_Worksheet").Range($(8 +
i)$1067)).Color

End For
End If

Thank you!