You seem to have been neglected by our community.
Yes, it can be done, if I understand your intention correctly. The
topography in your approach means, in a sense, the 3D chart. The first two of
the three values defininig each point specify the x- and y-coordinates (like
on a map); the third value is represented by prescribed colors or shades.
You must have your variables ranged in three columns. On the base of the
first two you will create a normal scatter or line chart (trivial, as you
mention) with suitable (full background) markers. The task then will be done
by the following macro, while the new basic Series in the chart has been
pre-selected:
Option Explicit
Sub ColorPointValues()
'Petr Bezucha, 2008
Dim MarkersFormula As String, MarkersCount As Long, _
PosDiv1 As Long, PosDiv2 As Long, ColorI As Long, _
YRange As Range, DecisValue As Double, I As Long
With Selection
If .ChartType <> xlXYScatter Then Exit Sub
MarkersFormula = .Formula
MarkersCount = .Points.Count
PosDiv2 = InStrRev(MarkersFormula, ",")
PosDiv1 = InStrRev(MarkersFormula, ",", PosDiv2 - 1) + 1
Set YRange = _
Range(Mid(MarkersFormula, PosDiv1, PosDiv2 - PosDiv1))
For I = 1 To MarkersCount
DecisValue = YRange(I).Offset(0, 1).Value
'--------------------------------
'here you must incorporate your decision process
'in the simplest case ColorI = DecisValue
'--------------------------------
.Points(I).MarkerBackgroundColorIndex = ColorI
.Points(I).MarkerForegroundColorIndex = ColorI
Next I
End With
End Sub
Macro uses the SERIES formula for the identification of Y-range, and its
neighbor, the Z-range. It is taking the points (markers) one by one, looks
into the Z-value – now DecisValue, and … the work now comes up for you: the
gist is to obtain proper MarkerBackgroundColorIndex from the offer of the
ColorIndex table. You can either insert one or a series of If’s, or Select
Case, etc. All depends on your demand and the number of your color
categories.
You may also decide on only Y-values. The necessary downgrade in the more
common macro is then … .Offset(0,0). … .
ColorIndex is the restriction, as for the color scale, of Excel 2003 and
downwards. With 2007 you can enjoy unchained RBG colors.
The details around colors you can find elsewhere.
Respond please if successful.
Regards
