Hi,

I have an Excel macro that creates a chart with flexible number o
lines (usually up to three lines) and flexible number of points fo
each line (may reach 10 points per line). All points and all line
should be in different colors.
Does someone know of a way to set colors dynamically in a wide colo
range that has good distinction between each color?

I tried the following code but when I have many points some colors ar
very similar and some are even white.

" Select Case nLineIndex
Case 0:
Select Case nPointIndex
Case 1:
GetPointColor = 3
Case 2:
GetPointColor = 10
Case 3:
GetPointColor = 53
Case 4:
GetPointColor = 39
Case Else:
GetPointColor = nPointIndex* 14 + 1
End Select
Case 1:
Select Case nPointIndex
Case 1:
GetPointColor = 5
Case 2:
GetPointColor = 52
Case 3:
GetPointColor = 46
Case 4:
GetPointColor = 20
Case Else:
GetPointColor = nPointIndex* 10 + 2
End Select
Case 2:
Select Case nPointIndex
Case 1:
GetPointColor = 4
Case 2:
GetPointColor = 13
Case 3:
GetPointColor = 33
Case 4:
GetPointColor = 45
Case Else:
GetPointColor = nPointIndex* 20 + 3
End Select
Case Else:
GetPointColor = nPointIndex* nLineIndex* 10 + nLineIndex
End Select"

Thanks,

Ana

--
Ana
-----------------------------------------------------------------------
Anat's Profile: http://www.excelforum.com/member.php...fo&userid=1670
View this thread: http://www.excelforum.com/showthread.php?threadid=31926

I have tried this, too - setting chart colors dynamically by some
calculation. The problem is that (for points and lines in charts) Excel uses
the color palette (56 colors only) - if you try to set a different value
Excel will convert it to the closest match in the palette - which is why some
of your colors look the same (or get converted to white).

If you can do with 56 colors, you can use the Colors property of the
workbook to change the palette to a range of colors to your liking. However,
this will change the color selection for many other things in your workbook
as well (such as cell interior colors, etc). The limitations on colors
and/or the effect on the rest of the workbook may cause problems, but I know
of no other solution.

"Anat" wrote:


Hi,

I have an Excel macro that creates a chart with flexible number of
lines (usually up to three lines) and flexible number of points for
each line (may reach 10 points per line). All points and all lines
should be in different colors.
Does someone know of a way to set colors dynamically in a wide color
range that has good distinction between each color?

I tried the following code but when I have many points some colors are
very similar and some are even white.

" Select Case nLineIndex
Case 0:
Select Case nPointIndex
Case 1:
GetPointColor = 3
Case 2:
GetPointColor = 10
Case 3:
GetPointColor = 53
Case 4:
GetPointColor = 39
Case Else:
GetPointColor = nPointIndex* 14 + 1
End Select
Case 1:
Select Case nPointIndex
Case 1:
GetPointColor = 5
Case 2:
GetPointColor = 52
Case 3:
GetPointColor = 46
Case 4:
GetPointColor = 20
Case Else:
GetPointColor = nPointIndex* 10 + 2
End Select
Case 2:
Select Case nPointIndex
Case 1:
GetPointColor = 4
Case 2:
GetPointColor = 13
Case 3:
GetPointColor = 33
Case 4:
GetPointColor = 45
Case Else:
GetPointColor = nPointIndex* 20 + 3
End Select
Case Else:
GetPointColor = nPointIndex* nLineIndex* 10 + nLineIndex
End Select"

Thanks,

Anat


--
Anat
------------------------------------------------------------------------
Anat's Profile: http://www.excelforum.com/member.php...o&userid=16706
View this thread: http://www.excelforum.com/showthread...hreadid=319263

I agree with K Dale's comments, just to expand:

To create 30 easily distinguishable colours for use in a chart is quite
difficult, particularly for multiple points in Line Series. I would suggest
two approaches for your three Series -

1) Three complete ranges of Hue tones, each range with a different level of
luminosity, or
2) Three base colours, a ranging from fairly light to fairly dark, but not
too light/dark.

For bar type charts I'd go for option 2, differences in shades doesn't need
to be much. But for your lines, maybe option 1.

I've made some colours for you to play with (with what I have takes a few
seconds!)

Sub ChartColours1()
Dim vIdxRow5, i As Byte, j As Byte
Dim v(1 To 3), x As Long

vIdxRow5 = Array(38, 40, 36, 35, 34, 37, 39, 2)
v(1) = Array(9671679, 9690879, 9699301, 14221203, _
16777107, 16762515, 16749530, 15176703)
v(2) = Array(255, 51199, 65478, 11271937, _
16776960, 16742656, 16711853, 14680319)
v(3) = Array(200, 39880, 51355, 8833280, _
13158400, 13131520, 13107335, 11469000)

For i = 1 To 3
For j = 1 To 8
If i = 1 Then 'row 5 colorindex
x = (vIdxRow5(j - 1))
ElseIf i = 2 Then x = j + 16 'row 6
Else: x = j + 24 'row 7
End If
ActiveWorkbook.Colors(x) = v(i)(j - 1)
''uncomment to view colors & index's on sheet
' Cells(i, j) = x
' Cells(i, j).Interior.ColorIndex = x
Next
Next
End Sub

Above should customize the bottom three rows of your workbook's palette. 3 x
8 colours, so you'll need to do a bit more if you have 10 points / series.

ColorIndex's in the top 5 rows of the palette are not in logical order,
hence the array of index's in "vIdxRow5". The bottom 2 rows are 17-32.
Having customized your palette you could use bits of this code to apply
colorindex's to your points, without all that Case stuff.

Regards,
Peter


"K Dales" wrote:
I have tried this, too - setting chart colors dynamically by some
calculation. The problem is that (for points and lines in charts) Excel
uses
the color palette (56 colors only) - if you try to set a different value
Excel will convert it to the closest match in the palette - which is why
some
of your colors look the same (or get converted to white).

If you can do with 56 colors, you can use the Colors property of the
workbook to change the palette to a range of colors to your liking.
However,
this will change the color selection for many other things in your
workbook
as well (such as cell interior colors, etc). The limitations on colors
and/or the effect on the rest of the workbook may cause problems, but I
know
of no other solution.

"Anat" wrote:


Hi,

I have an Excel macro that creates a chart with flexible number of
lines (usually up to three lines) and flexible number of points for
each line (may reach 10 points per line). All points and all lines
should be in different colors.
Does someone know of a way to set colors dynamically in a wide color
range that has good distinction between each color?

I tried the following code but when I have many points some colors are
very similar and some are even white.

" Select Case nLineIndex
Case 0:
Select Case nPointIndex
Case 1:
GetPointColor = 3
Case 2:
GetPointColor = 10
Case 3:
GetPointColor = 53
Case 4:
GetPointColor = 39
Case Else:
GetPointColor = nPointIndex* 14 + 1
End Select
Case 1:
Select Case nPointIndex
Case 1:
GetPointColor = 5
Case 2:
GetPointColor = 52
Case 3:
GetPointColor = 46
Case 4:
GetPointColor = 20
Case Else:
GetPointColor = nPointIndex* 10 + 2
End Select
Case 2:
Select Case nPointIndex
Case 1:
GetPointColor = 4
Case 2:
GetPointColor = 13
Case 3:
GetPointColor = 33
Case 4:
GetPointColor = 45
Case Else:
GetPointColor = nPointIndex* 20 + 3
End Select
Case Else:
GetPointColor = nPointIndex* nLineIndex* 10 + nLineIndex
End Select"

Thanks,

Anat


--
Anat
------------------------------------------------------------------------
Anat's Profile:
http://www.excelforum.com/member.php...o&userid=16706
View this thread:
http://www.excelforum.com/showthread...hreadid=319263