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

It might make more sense if you could attach it as an Excel sheet (go advanced>manage attachments). Maybe zip it if it's too big.

Stop me if I have any of this wrong.
Okay, so, when you choose the name and hours on your form page and hit "enter" you want the appropriate color to highlight that range on the appropriate day. You have your Timesheet set up with only 2 columns per date. Do you want to insert additional columns if more than 2 names insert data for that date? Then next to the name you want a sum of hours for each name.

Do you have a macro for any of this?

Okay Xide,
Here's what I did. On your Data sheet, I added the colorindexes for each of the names (this is pulled over to Form!A13)
For use in the macro, I have some Matches and Lookups in A10:A13 of your form. I used White font so they are not evident. ). A10 first row of timeperiod, A11, last row of time period, A12 is date column and A13 is the color index for the specific name.
Pressing the Enter Button runs this macro

Please Login or Register  to view this content.

Note Row 34 on "time sheet" will have 1's in the cells if that date column has already been used. Again, I set the font to white.
I didn't do anything with summing colors as you said you already had that covered.
Hope this helps you.