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.


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.


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.


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.


Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.