Home > Format > ConditionalFormatting > Colour

Conditional Formatting - Show List and Colors

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

change a color selection

Introduction

On a Microsoft Excel 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 cell rules, and data validation. The written instructions are below the video.

Video Timeline

  • 0:00 Demo of technique
  • 0:27 How to set up the file
  • 0:38 Created named ranges
  • 1:05 See range names in Name Box list
  • 1:22 Create drop down list of numbers
  • 1:54 Format the cell
  • 2:05 Create drop down lists of colors
  • 2:48 Type list of numbers
  • 3:04 Planning the formatting
  • 3:36 Format the number cells
  • 3:41 Add conditional formatting to number cells
  • 4:12 Type the conditional formatting formula
  • 5:30 Add conditional formatting to color cells
  • 5:54 Type the conditional formatting formula
  • 6:35 Select formatting options
  • 7:09 Add conditional formatting to Sample cells
  • 7:49 Select formatting options for Red
  • 8:07 Select formatting options for Yellow
  • 8:38 Select formatting options for Blue
  • 8:59 Test the worksheet
  • 9:22 Summary

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.

Note: You can add more colors in your workbook, like green colour, or orange. I kept this list short for the sample file.

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 specific number of Options..

You can use commands on the Excel Ribbon's Home tab to add the formatting, or press Ctrl + 1 keyboard shortcut, to open the Format Cells dialog box.

  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 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, click the drop down arrow in a cell, and change a color selection in column C. The adjacent cell in column should show the current selection color.go to top

formatted color cells

Format the Sample Cells

Next, you'll apply conditional formatting in the sample column, to show specific colors, based on the adjacent cell values. You will set up a separate rule for each color - Red, Blue and Yellow.

Rule for Red

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

  1. Select the range of 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, and checks if the value in C5 is equal to the text string, "red":

    =C5="red"

    In the formula, C5 is a relative reference (no dollar sign to lock the row or column), so it will adjust for each row in the list of numbers.

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

    formatted sample cells

Rule for Blue

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

Rule for Yellow

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

Get the Sample File

To see the completed file, and how it works, you can get 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

 

 

Last updated: June 20, 2023 4:11 PM