title: How can I vary the colour of a point on a scatter-graph in VBA/Excel as a function of the X, Y co-ordinates of the point itself?
body: <p>I am developing an Excel-based simulator that runs a specific test 1,000 times. The result of each test is two co-ordinates. These co-ordinates are written to a range. I then create an X-Y Scatter graph based on that range. </p>
  
  <p>What I now wish to do is to cycle through each of the 1,000 points of the graph, and change the colour of that point based on the X and Y co-ordinate of the point. Specifically, I want to take X Mod (Half Max X) and Y Mod (Half Max Y), in order to create the effect that the intensity of the colour will increase as the points near the middle of the graph.</p>
  
  <p>The code I want to use is as follows:</p>
  
  <pre><code>Temp = "'" + FirstCalcs.Name + "'!" + FirstCalcs.Cells(SOWD + 3, 2).Address + ":" + FirstCalcs.Cells(SOWD + 3, NumOfTests + 1).Address
  Temp2 = "'" + FirstCalcs.Name + "'!" + FirstCalcs.Cells(SOWD + 4, 2).Address + ":" + FirstCalcs.Cells(SOWD + 4, NumOfTests + 1).Address
  Set chtChart = Charts.Add
  With chtChart
  
      Do Until .SeriesCollection.Count = 1     ' remove extra series, which Excel spawns like rabbits, for some reason
          .SeriesCollection(1).Delete
      Loop
  
      .Name = Institution + " summary"
      .ChartType = xlXYScatter
      'Link to the source data range.
  
      '.SetSourceData Source:=FirstCalcs.Range(Temp)
  
      .SeriesCollection(1).Values = Temp2
      .SeriesCollection(1).XValues = Temp
  
      For Each pts In .SeriesCollection(1).Points
  
              .Format.Fill.Solid
  
              'The next line is what I can't figure out
              .MarkerBackgroundColor = RGB(XFunctionOf(.pts.XCoor),  YFunctionOf(.pts.YCoor), 128)
  
              .MarkerStyle = xlMarkerStyleDiamond
              .MarkerSize = 10
      End With
  
      Next i
  
      .HasTitle = True
      .ChartTitle.Text = "Summary"
      .Axes(xlCategory, xlPrimary).HasTitle = True
      .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "T"
      .Axes(xlValue, xlPrimary).HasTitle = True
      .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Time before T achieved"
      .HasLegend = False
  
  End With
  </code></pre>
  
  <p>However I can't work out a way to get the X AND Y value in order to perform the requisite function on them. I can vary the colour according to the number of the point itself (1 to 1000), but that is not what I want to do - the colour is a function of the X, Y co-ordinate of each point. It is worth saying that the ranges of both the X and Y co-ordinates are variable, i.e. the test doesn't return results which are on the same scale each time.</p>
  
  <p>Any help would be enormously appreciated and thank-you in advance. </p>