Conditional formatting - more than 3 conditions - result based on another cell

I have established that to have more than 3 conditions in my version of Excel (2003) i have to use VBA. Unfortunately I have very little knowledge of this process.

I have attached an example of the worksheet I am working on

The worksheet in question totals hours worked for payroll.

Problem:

Required result: Cell to change to 1 of 8 background colours in response to selection of 1 of 8 options in drop down list in the cell directly next it.

i.e. (in reference to attachment - sheet DATA)
IF E6 = Holiday, then D6 = Red
IF E6 = Half Day Holiday, then D6 = Red
IF E6 = Sick, then D6 = Blue
IF E6 = Sick Half Day, then D6 = Blue
IF E6 = Bank Holiday, then D6 = Green
IF E6 = Compassionate Leave, then D6 = Pink
IF E6 = Unpaid Leave then, D6 = Yellow
IF E6 = Unpaid Leave Half Day then, D6 = Yellow

However, the same basic table as shown in sheet DATA appears several times on the worksheet. And in each instance the conditional formatting is the same (in reference to the relative cells).

Does this make sense? This is my first post after several hours of research on the subject. Please advise if post is in wrong place/clarification is necessary etc. I have done my best to adhere to the forum rules.

Many, many thanks.

DAN COOPER

Right-click on the sheet where you want this to occur and select VIEW CODE.

Paste in this sheet event macro:

Please Login or Register  to view this content.

Press Alt-Q to close the VBEditor.
Save your sheet.

Give it a try. I've highlighted the part of the code where you indicate what range of cells to watch, so adjust that if you change the layout of your sheet.

Like so:

Please Login or Register  to view this content.