Change the cell fill color based on the selection from the drop-down list in the adjacent cell

Question

This is my first time here, so I hope everything is correct.

First of all, I’ve been doing this for a long time and found a lot of useful tips on manipulating cell colors in Excel, but none of them could complete the task.

Here is what I need to do:

I have a table that shows a weekly class schedule for my studies.

Each row contains data related to times, rooms, and programs. The last two cells of each row are the color code for the course and the course level. Let me explain below:

In cell H8, I want to have a fill color based on the selected level from the drop-down list in cell I8. (for example, 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 you find will only allow you to select "Yes / No" or "0/1". This is inadequate for me.

Items in the drop-down list in I8 and the corresponding color for cell H8;

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

I am really upset and would greatly appreciate any help finding a solution.

Thanks to everyone.

Answer 1

In Excel 2010, this is easy; it just takes a few steps for each list item.

The following steps must be performed for each item in the check list. (Open the worksheet for where the dropdown was created)

1) Click on the cell with the drop-down list.
2) Choose which answer to apply the format to. 3) Click the Home tab, then click the Styles tool button on the ribbon.
4) Click Conditional Formatting, select New Rule from the drop-down list.
5) Select the rule type: "Format only cells containing"
6) Change the rule Description: “Cell value”, “equal”, click the cell formula icon in the formula bar (on the right), select in which worksheet the scan list was created, select the cell in the list to which you want to apply formatting.

The formula should look something like this: ='Workbook Data'!$A$2

7) Click the formula icon again to return to the formatting menu.
8) Click the "Format" button next to the preview panel.
9) Select all the required format options.
10) Double click "OK".

You are finished with one item in the list. Repeat steps 1 to 10 until all the drop-down list items are complete.

Answer 2

You can use Conditional Formatting as follows.

• In cell H8 select Format> Conditional Formatting ...
• In Condition1, select Formula in the first drop-down menu
• In the following text box =I8="Elementary"
• Select Format... and select the desired color, etc.
• Choose Add>> and repeat steps 1 to 4.

Please note that you can only have (in excel 2003) three separate conditions so that you can only have different formatting for the three items in the drop-down menu. If the idea is to make them visually distinct, then (possibly) having no color for one of the options is not a problem?

If the cell is never empty, you can use the format (non-conditional) to get 4 different visual images.

Answer 3

You can try Conditional Formatting, available in the menu tool "Format → Conditional Formatting".

Answer 4

This works with me:
1- select the cells that should be influenced by the drop-down list.
2- home → conditional formation → new rule.
3-format only cells that contain.
4- in the format of only cells with ... select specific text, in the formatting rule "= select Elementary from the drop-down list"
if the drop-down list is in another sheet, then when choosing an elementary one, we will see "= Sheet3! $ F $ 2" in the new rule with your own sheet and cell number.
5- format → fill → select color → ok.
6-ok.
do the same for each item in the drop-down list, then you will see the magic!

Answer 5

this is the easiest way: to do list
Select list
right click: Define a name (e.g. ItemStatus)
select the cell in which the list should appear (you can copy the paste later, and not in a critical place)
Data> Data Validation
Allow: Select List
Source: = ItemStatus (don't forget the = sign)
click "OK" a drop-down list will appear in the selected cell
Home> Conditional Formatting
Rule management
New rule
etc.