Conditional formatiing will do what you want. But, and this is my personal preference, I <:hate:>, reasons
1) its easy to forget you've set it, if you have to maintain someone elses code it can be difficult to track down
2) its a pig to modify if your users suddently decide they want to chaneg the colours
3) you are limited in the number of conditions and appearance
4) its not very easy to copy the formulas, especially if you have 3 conditional formulas per cell
5) because the formatiing is not immediately visible it easy to make a mistake, and perpetuate an error
So becasue of that, and my prejeudice(!), I would put the code you want in the the "change", or "lostfocus" events. If you put the formatting as a VBA procedure then call the function or sub in these events. It becomes a write once use many, any changes to the formatting comes through automatically. To be safe I'd probaly add it to the "beforeupdate" code.......
If the formatting applies to exsiting records then add it to the forms "current" event so as you iterate through the record set it gets set.
Just in case you think im being irrational I had to change 2 monster reports which virtually every cell in the report had conditional formatting (very nearly 500 cells overall, took for ever to do. The report was fairly slow, replaced that with a VBA procedure which was quicker, allowed the users to change formats (background colours, text colour borders etc....). 'Cos some bright spark inmarketing always comes up with "wouldn't it be nie if we highlightes that condition in that colour, etc.....