|I have Excel 2003, so I can only test this with 3 conditions. I believe with 2007, you should be able to set up at least 4 Conditional Formatting rules.|
Here's what I did for 3 conditions in Excel 2003 - Modify this as required for 2007.
Using A1:C1 as an example, select the range.
Under Format...Conditional Formatting, set up 3 conditions using "Formula Is..." and choose a color for each:
Note the position of the "1" in each formula.
This will RANK the 3 values in A1:C1 and then use CHOOSE to find the "1". Based on which cell returns the "1", which CF will interpret as TRUE, CF will color the highest value Red , the middle value Yellow and the lowest value Green .
Equal values will have the same color based on where they rank.