I'm re-learning Excel after a many years absence and need some (understatement) help. I'm running Excel 2002 and am away from home and my apps manual!

I'm into Genetic Genealogy and I have a spreadsheet with 37 (can later be up to 67) columns listing the DYS allele values of over 200 people in the same Haplogroup. I want to compare my 37 allele values with these 200 people to see who match my allele value the closest, and hence, possibly, are "close" relatives.

The DYS allele values, in the 37 columns, range from a value of 9 to 35, but within each column has a much smaller range, say +/- 4. What I'm trying to do is to make the cells matching my allele values, in each of the 37 columns, to be GREEN, those with a value of 1 less to be ORANGE, 2 less BROWN, etc, so that I, at a glance, can identify the close matches.

With Conditional Formatting I can achieve the GREEN, but cannot easily copy the "formula" down the column, and with the =IF statement I cannot figure out how to achieve the cell colours.

Any help and suggestions gratefully received.