Contextures

Conditional Formatting - Show List and Colors

Use conditional formatting to show a list with a specific number of items, and then colour cells based on a selection from a drop down list. No macros are required, just built-in Excel features.

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.

conditional format color

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.

options worksheet

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

numbers and colors lists

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. go to top

naming the lists

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.

select NumList name

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

drop down list of numbers

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.

format 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.

create color drop downs

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

select from color drop downs

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:
  5. =B5<=$C$2

    In the formula, B5 is a relative reference, so it will adjust for each row in the list of numbers. $C$2 is an absolute reference, because each number cell should compare its value with cell C2-- it should not adjust in each row.

  6. Click the Format button
  7. On the Font tab, select Black as the font color
  8. On the Border tab, select an Outline border
  9. Click OK twice

    format number cells

To test the formatting, change the number of options in cell C2.

format number cells

Format the Color Drop Downs

Next add conditional formatting on the Color column, so the cell will have a blue fill and outline border if it's option number is visible.

To format the Color cells, when a selection in made in cell C2 (number of options):

  1. Select cells C5:C12
  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 Option Number cell (B5) for the active cell -- C5:

    =B5<=$C$2

    In the formula, B5 is a relative reference, so it will adjust for each row in the list of numbers. $C$2 is an absolute reference, because each number cell should compare its value with cell C2-- it should not adjust in each row.

  5. Click the Format button
  6. On the Fill tab, select light blue as the fill color
  7. On the Border tab, select an Outline border
  8. Click OK twice

    format color cells

To test the formatting, change the number of options in cell C2. Then, select a color for each option.go to top

formatted color cells

Format the Sample Cells

Next, use conditional formatting in the sample column, to show the selected colors. You will set up a separate rule for each color.

To format the Sample cells, when red is selected in column C:

  1. Select cells D5:D12
  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 Color cell (C5) for the active cell -- D5:

    =C5="red"

    In the formula, C5 is a relative reference, so it will adjust for each row in the list of numbers.

  5. Click the Format button
  6. On the Fill tab, select red as the fill color
  7. On the Border tab, select an Outline border
  8. Click OK twice

    formatted sample cells

To format the Sample cells, when blue is selected in column C:

  1. Select cells D5:D12
  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 Color cell (C5) for the active cell -- D5:

    =C5="blue"

    In the formula, C5 is a relative reference, so it will adjust for each row in the list of numbers.

  5. Click the Format button
  6. On the Fill tab, select blue as the fill color
  7. On the Border tab, select an Outline border
  8. Click OK twice

To format the Sample cells, when yellow is selected in column C:

  1. Select cells D5:D12
  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 Color cell (C5) for the active cell -- D5:

    =C5="yellow"

    In the formula, C5 is a relative reference, so it will adjust for each row in the list of numbers.

  5. Click the Format button
  6. On the Fill tab, select yellow as the fill color
  7. On the Border tab, select an Outline border
  8. Click OK twice

To test the formatting, change a color selection in column C, and the adjacent cell in column should show the selected color.go to top

change a color selection

Download the Sample File

To see the completed file, and how it works, you can download the zipped sample file:  condformatselcolor.zip

The file is in xlsx format, and does not contain macros.go to top

More Tutorials

Conditional Formatting Introduction

Conditional Formatting Based on another cell

Conditional Formatting Examples

Conditional Formatting Documentation

Conditional Formatting Data Bars

Search Contextures Sites

 

Excel Tools Add-in

 

 

Peltier Tech Charts for Excel 3.0

 

 

 

Excel Data Entry Popup List

 

Excel UserForms for Data Entry

 

Last updated: December 29, 2016 1:44 PM
Contextures RSS Feed