Here are the steps I followed to achieve what I was after.
- Select all the data that you want to colour code.
- Click on Conditional Formating on the Home Tab
- Choose Manage Rules as shown below
- From the dialog that appears click New Rule.
- Add the multiple conditions using the And function in the formula box
- The most important thing you need to do when you are adding the formula to highlight rows is to remove the $ sign that gets assigned when you choose the cell for entering a formula with a mouse.
- Choose the colour by clicking the format button and click ok
- Click Ok again to come out of the Manage Rules window.
As you can see below I have added two rules and it is giving me the result that I want.