Change the fill color of a cell based on a selection from a Drop Down List in an adjacent cell

Question

This is my first time here so I hope I'm doing things right.

First of all, I have been investigating this for quite a while, and have found many useful tips for manipulating cell colors in Excel, but none have been able to accomplish the task.

Here is what I need to accomplish;

I have a table which shows a weekly class schedule for my teaching.

In each row I have data pertaining to times, rooms, and programs. The last two cells of each row are the color code for the course and the level of the course. Let me explain below;

In cell H8 I want to have a fill color based on the selected level from a drop down list in cell I8. (e.g. If I select "Pre-Intermediate" from the drop down list in I8 I would like the fill color of H8 to change to 'Red')

The problem is that all the solutions I have found will only allow for a 'Yes/No' or '0/1' selection. This is inadequate for me.

The items in the the drop down list in I8 and the corresponding color for cell H8 are;

Elementary -> Blue  
Pre-Intermediate -> Red  
Intermediate -> Green  
Upper Intermediate -> Amber  

I am really getting frustrated, and would greatly appreciate any assistance to find a solution.

Thanks to all.

Answer 1

In Excel 2010 it is easy, just takes a few more steps for each list items.

The following steps must be completed for each item within the validation list. (Have the worksheet open to where the drop down was created)

1) Click on cell with drop down list.
2) Select which answer to apply format to.
3) Click on "Home" tab, then click the "Styles" tool button on the ribbon.
4) Click "Conditional Formatting", in drop down list click the "*New Rule" option.
5) Select a Rule Type: "Format only cells that contain"
6) Edit the Rule Description: "Cell Value", "equal to", click the cell formula icon in the formula bar (far right), select which worksheet the validation list was created in, select the cell within the list to which you wish to apply the formatting.

Formula should look something like: ='Workbook Data'!$A$2

7) Click the formula icon again to return to format menu.
8) Click on Format button beside preview pane.
9) Select all format options desired.
10) Press "OK" twice.

You are finished with only one item within list. Repeat steps 1 thru 10 until all drop down list items are finished.

Answer 2

You can leverage Conditional Formatting as follows.

1. In cell H8 select Format > Conditional Formatting...
2. In Condition1, select Formula Is in first drop down menu
3. In the next textbox type =I8="Elementary"
4. Select Format... and select the color you want etc.
5. Select Add>> and repeat steps 1 to 4

Note that you can only have (in excel 2003) three separate conditions so you will only be able to have different formatting for three items in the drop down menu. If the idea is to make them visually distinct then (maybe) having no color for one of the selections is not a problem?

If the cell is never blank, you can use format (not conditional) to get 4 distinct visuals.