VBA chart conditional formatting

Hi, I am a novice at VBA and have a question about formatting marker colors in a chart based on the values in a column of my spreadsheet.

I have results of analysis of soil samples in a worksheet with 27 columns and 360 rows. The column labels are for year, plot, and then 25 soil analysis variables. Each row is for one sample from one year--there are 8 years of data. There are 45 samples for each year--one sample for each of 45 plots, labeled "1" through "45" (in column D). What I have done is to use VBA to produce a X-Y chart for each soil variable: "TC" for Total Carbon, for example. The x-axis is categorical: "1" to "45" for each plot. The y-axis is the level of carbon in the soil sample for that plot. The chart therefore shows stacked markers above each plot number; one marker for each of the 8 years. I have stacked 25 charts--one for each variable--using VBA, by making the code for one variable's chart and then just copying down the VBA window 24 times, changing the refernce columns and names for each chart, and the chart's location on the sheet.

This works fine, but what I'd like to do is to have different marker colors for different years. I am thinking that the .FormatConditions object is the way to go (I have Excel 2007), but I can't figure out how to use it. Can anyone help me out with this?

I have posted below my VBA script for one of the charts. It's probable that this script shows how little VBA I know--I used the Macro Recorder a bit and copied from forums a bit.

Thank you!

______________________
Dim ChtTC As ChartObject
Set ChtTC = ActiveSheet.ChartObjects.Add(5, 2970, 600, 150)
With ChtTC.Chart
.ChartType = xlXYScatter
.SetSourceData Source:=Sheets("data").Range("ac5:ac320,e5:e320")
.SeriesCollection(1).Values = "='data'!$ac$6:$ac$320"
.SeriesCollection(1).XValues = "='data'!$d$6:$d$320"
.SeriesCollection(1).Name = "TC"
.SeriesCollection(1).MarkerSize = 4

With .Axes(xlCategory)
.MinimumScale = 0
.MaximumScale = 45
.CrossesAt = 0
End With

With .Axes(xlValue)
.MinimumScale = 0
.MaximumScale = 60
' .CrossesAt = 0
End With

.HasLegend = False
.HasTitle = True
.ChartTitle.Text = "TC"
.ChartTitle.Select
With Selection
.Font.Size = 8
.Top = 0
.Left = 0
End With
End With

OK, I'll try restating this more simply. I have the following spreadsheet and wish to plot these on an XY chart, with plot number on the x-axis and pH on the y-axis. For each marker that shows pH value, I wish to show a color that corresponds to each year. For example, the pH values from 2010 would be red; those from 2009 would be blue, etc.

I would like to create the chart using VBA, and here’s the important part: I would like to do this color-formatting by referring to the values of the year, rather than the range of each year. I actually have many years of data and many records, and the data change occasionally—so referring to the range would be very inefficient. I'd also like to keep this in database format (using just these three columns), rather than adding columns for each year's data. Anyone know a way to do this?

I have posted below an example "spreadsheet" and the code I’ve been using to make these charts—without the color option.

Thanks!

Year --Plot --pH
2010 --1 --6.93
2010 --2 --7.05
2010 --3 --7.15
2010 --4 --6.96
2009 --1 --7.25
2009 --2 --6.58
2009 --3 --7.18
2009 --4 --6.82
2008 --1 --6.60
2008 --2 --6.81
2008 --3 --7.19
2008 --4 --6.62

Dim Cht As ChartObject
'Dim ChtRnge As Range
lft_mgn = 5
top_mgn = 25
wdth = 600
hght = 150
param_rnge = "$a$5:$c$13"
param = "pH"

Set Cht = ActiveSheet.ChartObjects.Add(lft_mgn, top_mgn, wdth, hght)
With Cht.Chart
.ChartType = xlXYScatter
.SetSourceData Source:=Sheets("data").Range(param_rnge)
.SeriesCollection(1).Values = "='data'!$c$1:$c$13"
.SeriesCollection(1).XValues = "='data'!$b$1:$b$13"
.SeriesCollection(1).Name = pH
.SeriesCollection(1).MarkerSize = 4

With .Axes(xlCategory)
.MinimumScale = 0
.MaximumScale = 4
.CrossesAt = 0
End With

With .Axes(xlValue)
.MinimumScale = 4
.MaximumScale = 8
End With

.HasLegend = False
.HasTitle = True
.ChartTitle.Text = "pH"
.ChartTitle.Select
With Selection
.Font.Size = 8
.Top = 0
.Left = 0
End With

End With

Just click the icon