Shading a Cell Until Something is Entered

When creating a worksheet in which information must be entered into specific cells, you may find it helpful to shade the cells if they are blank, but have the shading removed if something is entered into the cell. You can easily accomplish this task by using the conditional formatting feature in Excel.

Follow these steps if you are using Excel 2007:

1. Select the cells to which the conditional formatting should apply.
2. Display the Home tab of the ribbon.
3. Click the Conditional Formatting tool in the Styles group. Excel displays a list of conditional formatting options.
4. Choose New Rule. Excel displays the New Formatting Rule dialog box.
5. In the list of rule types, select Use a Formula to Determine which Cells to Format. (Click here to see a related figure.)
6. In the formula area, enter the following formula, replacing A1 with the address of the active cell selected in step 1:

     =ISBLANK(A1)

7. Click Format to display the Format Cells dialog box.
8. Click the Fill tab. (Click here to see a related figure.)
9. Select the color you want used for shading the cell if it is blank.
10. Click OK to dismiss the Format Cells dialog box. The shading color you selected in step 9 should now appear in the preview area for the rule.
11. Click OK.

Follow these steps, instead, if you are using an older version of Excel:

1. Select the cell or cells that you want shaded if they are empty.
2. Choose Conditional Formatting from the Format menu. Excel displays the Conditional Formatting dialog box.
3. Use the Condition drop-down to choose Formula Is.
4. In the formula area, to the right of the drop-down list used in step 3, enter the following formula, replacing A1 with the address of the active cell selected in step 1:

     =ISBLANK(A1)

5. Click Format to display the Format Cells dialog box.
6. Click the Patterns tab.
7. Select the color you want used for shading the cell if it is blank.
8. Click OK to dismiss the Format Cells dialog box. The shading color you selected in step 7 should now appear in the preview area for the condition.
9. Click OK.

All the empty cells among those selected in step 1 should now appear shaded. When you enter something into one of the shaded cells, the shading should disappear.