Color formatting XY data points by value of another series.

You could set up two (or however many colours you need) more columns and use if statements so that they either plot the value in one column or the other, then plot these two columns on the chart.

e.g. If cell A2 downwards contains x-values, cell B2 downwards contains y-values, cell C2 downwards contains either 1 or 0 (to select which color to plot that data point).

Populate cell D2 with =IF(C2=0,B2,NA()) and cell E2 with =IF(C2=1,B2,NA()) and fill both columns down to the bottom of the data set. Then instead of plotting column A against B plot A against D and A against E and format the two series how you want them.

I have tried this but it just randomly varies the shape and fill.  I need all of the markers to stay the same shape and size but I need it to look at a second series with which to vary the color.  It would be analogous to the conditional formatting of a column of data but with data point.

Thanks for the input though,

Not rocket science but not trivial either.  If you are OK with code, do the following.

Define a range that will contain the conditional colors.  This range should be as follows:

Enter a series of ascending numbers in consecutive cells in a column.  These numbers correspond to the lower bound of range with the upper bound being the value in the next cell such that if the plotted value is > = cell value and < value in next cell then the color of the fill of that cell will be used to color the corresponding point.  An example should help.

I plotted a set of numbers between zero and 30.  In E2:E4 I entered the values 0, 10, and 20.  The first cell had a fill of red, the 2nd yellow, the 3rd green.  Note that these fills are the format of the cell and not a conditional format.

Then, select the chart that you want to conditionally color and run the doChartConditionalColor subroutine in the code below.  The code is lightly tested

Option Explicit

Sub doOnePoint(aPt As Point, Val, aRng As Range)

    Dim Rslt As Long

    On Error GoTo ErrXIT

    Rslt = Application.WorksheetFunction.Match(Val, aRng, 1)

    aPt.Format.Fill.ForeColor.RGB = aRng.Cells(Rslt).Interior.Color

ErrXIT:

    End Sub

Sub doOneSeries(aSeries As Series, aRng As Range)

    Dim I As Long

    For I = 1 To aSeries.Points.Count

        doOnePoint aSeries.Points(I), aSeries.Values(I), aRng

        Next I

    End Sub

Sub doChartConditionalColor()

    If ActiveChart Is Nothing Then _

        MsgBox "Please select a chart before running this routine": GoTo ErrXIT

    Dim Rng As Range

    Set Rng = Application.InputBox( _

        "Please select the range containing the 'conditional color'", Type:=8)

    If Rng Is Nothing Then GoTo ErrXIT

    If TypeOf Selection Is Series Then

        doOneSeries Selection, Rng

    Else

        Dim aSeries As Series

        For Each aSeries In ActiveChart.SeriesCollection

            doOneSeries aSeries, Rng

            Next aSeries

        End If

ErrXIT:

    End Sub

I have what is basically a 3 dimensional set of data to plot.  I would like to plot the main XY plot and have the color of the data points vary by the value of another column.  This would be like conditional formatting the data points by the second X category.

How can I do this?

Tushar Mehta (Technology and Operations Consulting)

www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)

Microsoft MVP Excel 2000-Present

Right click the data series on the chart and choose Format Data Series, Marker Fill, and check Vary Color by point.

If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire