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 - gt  Blue   Pre-Intermediate - gt  Red   Intermediate - gt  Green   Upper Intermediate - gt  Amber     I am really getting frustrated  and would greatly appreciate any assistance to find a solution   Thanks to all

User · Answer

You can leverage Conditional Formatting as follows    In cell H8 select Format   Conditional Formatting    In Condition1  select Formula Is in first drop down menu In the next textbox type  I8  Elementary  Select Format    and select the color you want etc  Select Add gt  gt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to excel

Examples related to excel-2007

Examples related to excel-formula