Marking different data types in plotted data

**mstrnoob** · 09-02-2008, 02:26 PM

I a using Excel 2000 at work and am graphing historical data. The data was recorded every month for the past 12+ years. Looks like

"area 1" "month 1" "month 2" .... "month x"
"area 2" "month 1" "month 2" .... "month x"
"area ..." "month 1" "month 2" .... "month x"
"area x" "month 1" "month 2" .... "month x"

There are random months in each year that the collected data was either invalid or guessed so its cell is highlighed yellow. Is there a way that I can graph the all of the data and "invalid" data, the actual data marker on the graph, would be a different color or marker type?

**Andy Pope** · 09-03-2008, 05:09 AM

It's very hard to tell from the data posted, a workbook with example data would be more helpful.

To get different coloured markers you can either use additional series, plotting only relevant points. Or vba code.

Is the colouring of the invalid cells done via Conditional formatting or just manually?

**mstrnoob** · 09-03-2008, 02:41 PM

Attached is a small example of what I am working with. There is a large amount of data so seperating valid and invalid data into different rows, and then graphing, would be too messy and confusing.

All data and highlighting is/was done manually. Basically the highlighting indicates that the data was not physically measured/recorded but was approximated.

Each row is plotted in a seperate graph. I am wondering if there is a way to mark the "invalid" cells so when the row is plotted, using excel's built in chart, that at each data marker is a different color and/or marker type.

If that can not be done "automatically" but can only be done using a vb script, can a cell be marked somehow to indicated in the vb code that the plotted data is invalid? I'll probaby have to checkout a book from the library.

**Andy Pope** · 09-03-2008, 03:31 PM

If you do not want to use additional series coding is the only way.

Code:

Sub ColourMarkers(MySeries As Series, CheckData As Range)

    Dim pntTemp As Point
    Dim lngIndex As Long
    
    On Error GoTo ErrColorMarkers
    
    For Each pntTemp In MySeries.Points
        lngIndex = lngIndex + 1
        If CheckData.Cells(lngIndex).Interior.ColorIndex <> xlNone Then
            pntTemp.MarkerBackgroundColorIndex = CheckData.Cells(lngIndex).Interior.ColorIndex
        End If
    Next
    
ErrColorMarkers:
    Exit Sub
    
End Sub

Sub Main()
    
    Dim rngChart As Range
    
    For Each rngChart In Worksheets("Loads").Range("A5:A9")
        ColourMarkers Charts(rngChart.Value).SeriesCollection(1), rngChart.Offset(0, 1).Resize(1, 224)
    Next
End Sub

Notice the effect settng individual points has on a legend with only 1 series.
Although the series is actual redundent with only 1 series, unless you add another dummy series to explain the colour variation in markers.

**mstrnoob** · 09-08-2008, 02:06 PM

Okay cool. Thank you for being very helpful. It is nice to see in the different marker colors in the plot. I have also checked out some excel scripting books to better understand how to create and run scripts.