Highlight row based on drop down list

Conditional Formatting - Show List and Colors

Select a colour name from a drop down list, and the next cell fills with the selected colour. There are no macros, just data validation drop downs and conditional formatting rules.

Introduction

On a data entry sheet, select a number of options from a drop down list. That number of options show up in the data entry area. Then, select a color for each item, from another drop down list. In the cell to the right, the selected color is shown.

Watch this video to see the steps for creating this worksheet, with conditional formatting and data validation. The written instructions are below the video.

Set Up the Workbook

In the workbook, there is an Options sheet, with headings, and this sheet is where the drop downs and formatting will be added.

On the Lists sheet, there are 2 lists -- a list of 8 numbers and a list of three colors.

Name the Lists

To use these lists in data validation drop down lists, create a named range for each list.

To name the numbers list:

1. On the Lists sheet, select all the numbers
2. Click in the Name box, and type a one-word name -- NumList
3. Press Enter, to complete the name

To name the colors list:

1. On the Lists sheet, select all the colors
2. Click in the Name box, and type a one-word name -- ColorList
3. Press Enter, to complete the name.

Create the Options Drop Down

On the Options sheet, create a drop down list of numbers, so people can select a specifi number of Options..

1. Select cell C2, where the list will appear.
2. On the Ribbon's Data tab, click Data Validation
3. For Allow, select List
4. Click in the Source box, and press the F3 key, to open the Paste Name window
5. Select NumList, and click OK, twice.

A drop down list of numbers now appears in the Number of Options cell.

Format the cell

Next, format the cell, to make it clear that the cell is for data entry.

1. Select cell C2
2. Format the cell with a light blue fill color
3. Add an Outside border to the cell.
4. Center the text in the cell.

Create Color Drop Downs

Next, set up the 8 cells with drop down lists of colors.

1. Select cells C5:C12
2. On the Ribbon's Data tab, click Data Validation
3. For Allow, select List
4. Click in the Source box, and press the F3 key, to open the Paste Name window
5. Select ColorList, and click OK, twice.

A drop down list of colors now appears in each of the Color cells.

Create List of Option Numbers

Next, create a list of numbers on the Options sheet

1. In cells B5:B12, type the numbers 1 through 8

Next, the option numbers will be formatted:

• When the sheet is first opened if nothing is selected for the number of options, no Option numbers should appear.
• When someone selects a number of options, those numbers should appear in black font, with an outline border.

To hide the numbers if nothing is selected for the number of options:

1. Select cells B5:B12
2. Change the font color to white
3. Change the fill color to white

To show the applicable numbers, when a selection in made in cell C2 [number of options]:

1. Select cells B5:B12
2. On the Ribbon's Home tab, click Conditional Formatting, New Rule
3. Click Use a Formula to Determine Which Cells to Format
4. In the formula box, type the following formula, which refers to the active cell -- B5:

=B5