Conditional Formatting from text

Hi Can someone help me translate the information below into a formula please? Im using excel 2007 and Im stuck!!!


If D2 has the text ‘yes’ and the range E2-J2 all contain all ‘yes’ fields then N2 displays the words ‘Green - Live’
If D2 has the text ‘yes’ and the range E2-J2 all contain some ‘no’ fields then N2 displays the words ‘Amber- Live’
If D2 has the text ‘Yes’ and the range E2-J2 all contain all ‘no’ fields then N2 displays the words‘Red- Live’
If D2 has the text ‘no’ and the range E2-J2 all contain all ‘no’ fields then N2 displays the words ‘Red- Not Live’
If D2 has the text ‘no’ and the range E2-J2 all contain some‘yes’ fields then N2 displays the words ‘Amber- Not Live’
If D2 has the text ‘no’ and the range E2-J2 all contain all ‘yes’ fields then N2 displays the words ‘Amber- Not Live’


Thank you very much for your help in advance