Advertisement
Advertisement


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.

2011/01/04
1
5
1/4/2011 5:28:03 AM


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.

2012/06/21

You could try Conditional Formatting available in the tool menu "Format -> Conditional Formatting".

2011/01/04

This works with me :
1- select the cells which shall be be affected by the drop down list .
2- home -> conditional formating -> new rule .
3- format only cells that contain .
4- in format only cells with ... select specific text , in formatting rule "= select Elementary from your drop down list"
if drop list in another sheet then when select Elementary we 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 element in drop down list then you will see the magic !

2014/01/23

this is the easiest way: Make list
Select list
right click: Define Name (e.g. ItemStatus)
select a cell where the list should appear (copy paste can be done later, so not location critical)
Data > Data Validation
Allow: Select List
Source: =ItemStatus (don't forget the = sign)
click Ok
dropdown appears in the cell you selected
Home > Conditional Formatting
Manage Rules
New Rule
etc.

2015/02/24

Source: https://stackoverflow.com/questions/4590909
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]