Why does Excel keep filling color?

With Microsoft Excel, you can simplify data entry by adding a drop-down list to a cell, allowing users to pick an option from the list instead of entering data. You can further tailor the spreadsheet by using conditional formatting to fill the cell with a specific color depending on which list item the user selects. The colors in the column can help users to quickly identify which list items are entered.

Create the List

  1. Type the list entries you want to use in a single column in the order you want them to appear. Make sure there are no blanks between entries. You can create this list on any page in the current workbook.

  2. Select the list of entries, and then right-click and choose "Define Name." Type a name for the list in the Name box, omitting any spaces between words, and then click "OK."

  3. Select the cell where you want the list. Select the "Data" tab, click "Data Validation" in the Data Tools group, and then choose "Data Validation."

  4. Select the "Settings" tab, choose "List" in the Allow box, type "=" followed by the list name in the source box, and then click "OK." The list is available in the selected cell.

Add Fill Colors

  1. Select the cell containing the list. Click "Conditional Formatting" in the Styles group of the Home tab.

  2. Point to "Highlight Cells Rules," and then choose "Text That Contains."

  3. Enter the first list item in the "Format Cells That Contain the Text" box. Choose the fill color from the list or click "Custom Format" at the bottom of the list to select a custom font and fill color. Click "OK."

  4. Repeat for each list item and fill color you want to apply. Save the worksheet before closing it.

If you're experiencing issues with the fill color not displaying properly in Excel, there are a few things you can do to fix the problem.

  1. First, make sure that the cells you're trying to fill are actually selected. If they're not, the fill color won't show up.
  2. Next, check the fill color palette to see if the color you're trying to use is actually available. If it's not, you won't be able to use it.
  3. If both of those things are set up correctly and you're still having trouble, try restarting Excel. Sometimes the program just needs a fresh start in order to work properly.

Hopefully one of those solutions will fix the issue and you'll be able to use the fill color function in Excel without any problems.

Why does Excel keep filling color?

Written by Allen Wyatt (last updated February 28, 2022)
This tip applies to Excel 2007, 2010, 2013, and 2016


Mander has a worksheet for which he wants to change the background color. Once changed, he wants the color to be "unchangeable" by anyone else. Mander knows he can use worksheet protection, but that stops people from actually using the worksheet. He doesn't want to stop use of the worksheet; he just doesn't want the background changed once he sets it.

There are a couple of ways you can go about achieving what you want. The first is to examine how you are applying your worksheet protection. The background color is considered a format of a cell, so all you need to do is to make sure you protect the worksheet and allow all changes except to the formatting. Follow these steps:

  1. Select the cells whose contents you want the user to be able to change. (You can select all the cells in the worksheet, if desired.)
  2. Display the Home tab of the ribbon.
  3. Click Format in the Cells group, and then choose Format Cells. Excel displays the Format Cells dialog box.
  4. Make sure the Protection tab is displayed. (See Figure 1.)
  5. Why does Excel keep filling color?

    Figure 1. The Protection tab of the Format Cells dialog box.

  6. Clear the Locked checkbox.
  7. Click on OK to close the dialog box.
  8. With the Home tab of the ribbon still displayed, click Format in the Cells group, and then choose Protect Sheet. Excel displays the Protect Sheet dialog box. (See Figure 2.)
  9. Why does Excel keep filling color?

    Figure 2. The Protect Sheet dialog box.

  10. Select all the check boxes except those related to formatting (Format Cells, Format Columns, and Format Rows).
  11. You do not need to enter a password unless you want to; just click on OK.

Now people can make any changes to whatever cells were selected in step 1, with the exception of changing any formatting. This means that your background color remains protected.

There is a problem with this approach—you may want to allow the user to change other cell formatting, such as how numbers, dates, or times are displayed. Since you've protected the formatting of the sheet, however, these things cannot be changed.

To get around this, the only thing you can do is to use a macro-based approach. A simple way is to create an event handler that modifies the background color of the worksheet whenever it is activated. This way, if someone modifies the background color, the next time the worksheet is activated those changes will be effectively done away with. Here's an example that sets the background color to a turquoise color:

Private Sub Worksheet_Activate()
    With Cells.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = RGB(0, 192, 192)
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

This macro needs to be added to the code sheet for whatever worksheet you want it used with.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (4268) applies to Microsoft Excel 2007, 2010, 2013, and 2016.

Author Bio

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. Learn more about Allen...

MORE FROM ALLEN

Picking a Contiguous Range of Table Cells

Creating a table in Word is a relatively simple task. When you want to format or edit information in the table, often the ...

Discover More

Getting the Name of the Parent Workbook

If you need to insert into a cell the name of the workbook in which a worksheet is contained, you can use the CELL ...

Discover More

Making Modal Dialog Boxes Appear in Front of Workbooks

Perhaps the most common way of communicating with programs is through the use of dialog boxes. We expect dialog boxes to ...

Discover More

How do I stop Excel from auto filling color?

Remove cell shading.
Select the cells that contain a fill color or fill pattern. For more information on selecting cells in a worksheet, see Select cells, ranges, rows, or columns on a worksheet..
On the Home tab, in the Font group, click the arrow next to Fill Color, and then click No Fill..

How do I stop Excel from auto filling cells?

If you want to stop automatic completion, you can turn this option off..
Click File > Options..
Click Advanced, and then under Editing options, select or clear the Enable AutoComplete for cell values check box to turn this option on or off..

Why does Excel keep highlighting cells?

This can be caused by the wheel on your wheelmouse being stuck in a down position or by hitting the F8 key. Either of these actions turns on "Extend selection". You'll likely be able to tell right away if that's the cause. Look in the Status bar, and you'll see EXT.