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. Show Create the List
Add Fill Colors
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.
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. Written by Allen Wyatt (last updated February 28, 2022) 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:
Figure 1. The Protection tab of the Format Cells dialog box. Figure 2. The Protect Sheet dialog box. 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 BioWith 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 ALLENPicking 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.
|