Show Color With Conditional Formatting

I like to color data entry cells, so they’re easy to spot on an Excel worksheet. If you know that you should type a value in every blue cell, it’s quicker, and safer, to fill in a worksheet.

Recently, I was asked how to color a specific number of data entry cells, based on a number that someone selected from a drop down list. So, instead of manually coloring the data entry cells, I used conditional formatting to color them.

In the screen shot below, 3 was selected in cell C2, so 3 data entry cells are blue, in cells C5:C7.

condformatcolor02

Select a Color

Another feature in this workbook is the drop down list of colors in column C. After you select a color, a sample of that color appears in column D. Select a different color from the drop down, and the sample color will change.

This is also done with conditional formatting – no macros required. There are 3 colors set up in the sample file, with a separate rule for each color. You could set up more rules if necessary.

condformatcolor16

Detailed Instructions

On my website, you can follow the step-by-step written instructions, to see how to set up the workbook, with lists in named ranges, and add the data validation and conditional formatting. Or, watch the video below, if you’d prefer to see me build the workbook, rather than read the steps.

condformatcolor05

Video: Conditional Formatting Colors

To see the steps for setting up the workbook, and adding drop down lists and conditional formatting rules, please watch this video tutorial.

Or Watch on YouTube: Color Excel Data Entry Cells With Conditional Formatting

Download the Sample File

To see how the conditional formatting works, you can download the sample file from my Contextures website. The zipped file is in xlsx format, and does not contain macros.

_______________________

You may also like...

3 Responses

  1. Lynda Maynard says:

    I’ve done this with some of my knitting and cross-stitch patterns, when I wasn’t sure what colors I wanted to use. Then I can check out the different colorways with a simple CTRL+H, instead of having to click on all the teeny-tiny little squares. And of course I’ve got a macro to set the official RGB values for the DMC embroidery thread…

  1. September 29, 2014

    […] Show Color With Conditional Formatting […]

Leave a Reply

Your email address will not be published. Required fields are marked *