Excel 2007 : Apply conditional formatting to a row according to 1 of 3 results in specific cell

Hi,
I've googled this question already and can't find an answer that applies, I also have NO experience writing macros in visual basic so hoping there's another way.

I use excel 2007 to keep a record of my gerbil keeping and breeding. ID code, name, gender, age, DoB, genetics/colour, when adopted/rehomed or living here, DoD, and comments on pairings, health etc. I use formulas to work out each animal's age to the day, and an If formula in a hidden column to identify if an animal is deceased, adopted/rehomed, or living here. The results to this formula are NO (animal is deceased), FALSE (adopted/rehomed but living) or YES (living here).

I have already used conditional formatting to highlight the box containing the gender info as Male, Female or Unknown (animal is still in nest).

I want the spreadsheet to highlight the (rest of the) row in one of 3 colours according to the result in the last formula row NO, FALSE or YES. (If necessary I will omit the colour formatting for gender as it is more important for me to quickly identify the animals I have).

I've tried conditionally formatting the whole spreadsheet using a 3-colour scale with a formula =$J4="YES" etc. but the programme tells me I can't use absolute cell referencing in colour scales. J is the column the formula is in, but as it is normally hidden I can move it if required. Row 4 is the first gerbil's data below headers and titles, my 2009 sheet has 26 animals, but I will have to update records back to 2003 once I've sorted it. (Over 100 records!) That's why I tried to do the whole sheet at once, as doing it individually for each record destroys the point of the formula; I'd be quicker to colour it myself. I don't know if I'm writing the formula correctly, or if it would be possible to copy the correct formula down the rows if I'm using conditional formatting rather than a standard formula anyway? ie. J4 J5 J6...

I'm willing to use alternate formulas/methods or rearrange my data as long as the result is that each row is one of three colours as described. I can't write visual basic programming if that's the only way, but I could work out/follow instructions/google how to incorporate it if someone fancies the challenge! Although I keep reading that conditional formatting can be applied when you only have 3 variations, so surely a macro isn't the only way?

Any help gratefully received from those wiser than me! I'm fairly competent on excel, just been a bit thrown by the change from 2003 to 2007. If needed, I can attach the document to clarify.

Thankyou,
Sarah

Hi Sarah,

Yes it might be more useful if you attached the workbook in question so that we can see your request in context. Just identify which cells you expect to be coloured and what the rule is for the colour. In 2007 you can have more than the three conditional formats that are the limit with earlier versions.

Regards

Hi Sarah,

Hopefully I've interpreted your request correctly.
To modify the colours just go to the column J cells, choose the Conditional formatting option, select the rule in question and change format as appropriate. Then just copy that cell and use Paste Special Formats to paste the CF across columns B:J

Regards