Changing a cells background colour based on a form entry.

TemplateExample.xlsxI have been strugling to find a solution to this problem for several days now and so I thought I should ask for some help.

Problem (Part 1)

I need to find a way of using data entered in a form to change the background colours of a spreadsheet. The form and the spreadsheet can be seen below.

\1
The Spreadsheet

\1
The Form

Here is an example of how a form entry should change the spreadsheet.

\1
The Form entry

\1
The changed spreadsheet

Problem (Part 2)

If a second form entry clashes with one already on the spreadsheet then it needs to be moved to the column next to it. Exmaple below.

\1
The Form entry

\1
The changed spreadsheet

Any help on this problem would be appreciated. And if you are unclear on anything or would like more pictures please feel free to ask.

EDIT: Here is the file TemplateExample.xlsx