title: Creating a chart from multiple series with the same Y value
body: <p>I need to create a chart to show coverage by different people over time.  For example, a scatter chart with no markers showing with one series for each time period that someone will be available.  Each person has a different Y value (e.g. Joe=1, Pat=2, Kevin=3).  The purpose is to visualize gaps in coverage where time is the X axis.</p>
  
  <p>I’ve found how to add each series through…</p>
  
  <pre><code>    ActiveChart.SeriesCollection.NewSeries
      ActiveChart.SeriesCollection(1).XValues = “=Sheet1!$A$1:$B$1”
      ActiveChart.SeriesCollection(1).Values = “=Sheet1!$C$1:$D$1”
      ActiveChart.SeriesCollection.NewSeries
      ActiveChart.SeriesCollection(2).XValues = “=Sheet1!$A$2:$B$2”
      ActiveChart.SeriesCollection(2).Values = “=Sheet1!$C$2:$D$2”
  </code></pre>
  
  <p>…and so on, but that code was generated by manually assigning X and Y values.</p>
  
  <p>What I need to do is:</p>
  
  <p>1) have all the X and Y values brought in (plotted on the chart) dynamically based on the number of rows on the worksheet (the number of rows will not always be the same), and</p>
  
  <p>2) format all the series with the same Y value in the same way (e.g. all series where Y=2 are red lines, and all series where Y=1 are blue lines).</p>
  
  <p>I was thinking that I could use a loop using i for iteration/row number until there's an empty cell, but I've been unable to make that work in this instance.</p>
  
  <p>The next step is being able to add data from multiple worksheets on the same chart.  I found some code that went like this...</p>
  
  <pre><code>    Dim ws As String
      For Each ws in ThisWorkbook
  </code></pre>
  
  <p>Will that take data from all worksheets?</p>
  
  <p>I'd like to post an example of what I'm trying to do, but apparently I need rep first.  Here is a link.  The only difference is that I want everything on the same line to be the same color (i.e. on the graph with only 2 objects in the legend).
  <a href="http://i280.photobucket.com/albums/kk185/navytoolmanshortfieldbreak/Graph%20Problem%201_zpszxpsgikn.jpg" rel="nofollow">http://i280.photobucket.com/albums/kk185/navytoolmanshortfieldbreak/Graph%20Problem%201_zpszxpsgikn.jpg</a></p>
  
  <p>Update:  If you can't get to the photo, there are 4 rows and 4 columns of data.  Each row contains a series that should be plotted as a horizontal line.  The first two columns are the X coords, while the last two columns are the Y coords.  The first row shows a line from 1,1 to 3,1, the second row from 5,1 to 7,1, etc.</p>
  
  <pre><code>    1    3    1    1
      5    7    1    1
      2    4    2    2
      7   10    2    2
  </code></pre>