Cells with different colours.

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.

Thank you Portuga,
Yes, I'm using Absolute Reference for my allele values on the top row of the sheet, and this is the value I want to use to compare with the other 200 values in the same column so I cannot see how this suggestion can be correct.

When I copy the Conditional Formatting down a blank column and manually enter values in that column I get the formatting to work correctly, but if I format the top row in a populated column and copy the formatting downward it overwrite the values with the value in the first row (which I guess is inevitable). Also, if I format a blank column and paste values into it the formatting is lost.

I think I need to use the logic operators to achieve what I want, but so far I have had no success. Can anyone point me in the right direction?

Correction/Addition to my previous post.

Have now found the "Format Painter" tool and I can now copy the formatting to a populated worksheet. The only, minor, problem now is that the Conditional Formatting only allows 3 condition, i.e. I can only do =, < and >, than my allele values which then converts to 3 different cell colours. It looks good, but if someone can offer a solution with more options I will be delighted.