How to Fill Blank Cells with Color in Excel – 5 Methods

This is the sample dataset.

There are several blank cells in the dataset.

Method 1 – Using the Go to Special Command to Fill Blank Cells with Color

Use the Go To Special command to fill blank cells with a color.

Steps:

• Select the dataset. If it is too big, you can select a cell and press ‘Ctrl+A’.

• Go to the Home tab.
• Click Find & Select in Editing.
• Select Go to Special.

• In Go to Special, select Blanks.

• Click OK.
• All blank values will automatically be selected.

• Go to the Home tab.
• Select Fill in Font.

• Select a fill color.

Blank cells are filled with color.

Read More: How to Fill Blank Cells with 0 in Excel

Method 2 – Fill Blank Cells with Color Using the Conditional Formatting in Excel

Steps:

• Select the dataset. If it is too big, you can select a cell and press ‘Ctrl+A’.

• Go to the Home tab.
• In Styles, click Conditional Formatting.
• Select New Rule.

• In New Formatting Rule, in Select a Rule Type, select Format only cells that contain.
• Choose Blanks in Format only cells with.
• Click Format.

• In Format Cells, go to the Fill tab and select a color.

• Click OK in Format Cells and New Formatting Rule. 

Method 3 – Fill Blank Cells with Color Using a Formula

Use  the ISBLANK and LEN functions or a reference blank cell to find blank cells in a dataset.

The ISBLANK function takes a cell as an argument and returns a Boolean value: blank or non-blank. The LEN function returns the length of the string value.

Steps:

• Select the dataset. If it is too big, you can select a cell and press ‘Ctrl+A’.

• Go to the Home tab.
• In Styles, click Conditional Formatting.
• Select New Rule.

• In New Formatting Rule, in Select a Rule Type, select Use a formula to determine which cells to format.
• In Format values where this formula is true, enter the following formula.

Tou can also use the following formula:

• Click Format.

• In Format Cells, select a color in Fill.

• Click OK in Format Cells and New Formatting Rule to save the changes. Blank cells will be filled with color.

Method 4 – Embedding a VBA to Fill Blank Cells with Color in Excel

Steps:

• Go to the Developer tab and select Visual Basic in Code.

• In the VBA window, select Insert and choose Module.

• Enter the code.

Sub Fill_Blank_Cells()
Selection.SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 4
End Sub

The color index is selected as 4.

• Save and close the VBA window.
• To run the code, select the dataset.

• Go to the Developer tab and select Macros in Code.

• In the Macro box, select the code name (Fill_Blank_Cells).
• Click Run.

Blank cells are filled with color.

Read More: How to Fill Blank Cells with Value Above in Excel VBA

Method 5 – Using VBA to Fill Blank (but Not Empty) Cells with Color

Steps:

• Go to the Developer tab and select Visual Basic in Code.

• In the VBA window, select Insert and choose Module.

• Use the following code.

Sub Fill_Blank_Cells_2()

  Dim rnge As Range
  Set rnge = Selection

  For Each cell In rnge
    If cell.Text = "" Then
       cell.Interior.ColorIndex = 20
    Else
       cell.Interior.ColorIndex = xlNone
    End If
  Next

End Sub

• Save and close the VBA window.
• Select the dataset.

• Go to the Developer tab and select Macros in Code.

• Select the code: Fill_Blank_Cells_2 in Macro name.
• Click Run.

Blank cells and all cells with a white space are filled with color.

Download Practice Workbook

Download the workbook.

Related Articles

• How to Fill Blank Cells with Value Below in Excel
• How to Fill Blank Cells with Value Above in Excel
• Fill Blank Cells with Dash in Excel
• How to Fill Blank Cells with Value from Left in Excel

<< Go Back to Fill Blank Cells | Excel Cells | Learn Excel