Excel chart; marker color based on cell value

[beakerboy]

I have a worksheet with three columns; an x value, a y value and a confidence value. I'd love to be able to plot the x and y in a scatter plot, and use the confidence value somehow in the marker color. I could easily add 3 more columns for RGB values and manipulate them using the confidence if there's a way to embed RGB values into the chart.

Thanks for any advice.
Kevin

 

[SkipVought]

hi,

Charts can be configured such that EMPTY cells do not plot.

Picture your table with several extra oolumns that will be used for color, each column represents ONE COLOR.

In each column is a formula with the logic...
[tt]
IF [the confidence value] is within some range THEN the value OTHERWISE EMPTY
[/tt]
So depending on [the confidence value], [the confidence value] fill appear in only ONE COLOR COLUMN. You do NOT plot [the confidence value], rather the color columns.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[beakerboy]

I guess that might be the simplest way. I was trying this:

Sub set_color()
    ActiveSheet.ChartObjects("Chart 3").Activate
    ActiveChart.SeriesCollection(1).Select
    For i = 1 To 24
        ActiveChart.SeriesCollection(1).Points(i).Select
        Dim color
        color = Cells(i, 4).value
        With Selection.Format.Fill
            .Visible = msoTrue
            .ForeColor.RGB = RGB(color, 0, 0)
            .Transparency = 0
            .Solid
        End With
    Next i
End Sub

But it sets every cell to the same default blue color.

 

[beakerboy]

Got It. I rearranged the function and it works

Sub set_color()
    ActiveSheet.ChartObjects("Chart 3").Activate
    For i = 1 To 24
        Dim color
        color = Cells(i, 4).value
        ActiveChart.SeriesCollection(1).Points(i).Format.Fill.ForeColor.RGB = RGB(color, 0, 0)
    Next i
End Sub

 

[SkipVought]

Well you did not post in forum707, so I did not suggest a VBA solution.

Besides, this is a perfectly good approch. I often use this kind of technique for plotting the demand and supply picture for a part in an MRP system. I can show individual demands and supplies as distinct columns (minus RED & plus GREEN) on the primary axis and the total shortage or surplus as columns with ZERO gap (minus PINK & plus LT GREEN) on the secondary axis, and you can IMMEDIATELY see the area(s) where attention must be directed.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]