More than 3 formatting conditions - select case?

Thank you for the help Roy but I am really struggling to understand how your example works. I think my main trouble, aside from not know a huge amount of vb code, is *where* to put the code within each workbook to make it do what I want! I didn't see that you'd attached another file, so will go and have a look at that now.

Thank you - I think I am beginning to understand. In this instance, we will always be pulling in data from access to excel, running the macro to do various formatting and date calculations, then I wanted to add the colour formatting on afterwards - will your addition still work? I decided the spreadsheet was far too messy with too many test macros recorded, so I have changed to another one and will add your code in where you did on the last example - I did get Shijesh's code to work perfectly for the colour coding I want, but I have to run a separate macro for that, when the cursor is in the right place!