Color blank cells

How to highlight blank cells using Excel and VBA methods

METHOD 1. Color blank cells using Conditional Formatting

EXCEL

Select range > Home tab > Style group > Click on Conditional Formatting > New Rules > Select Use a formula to determine which cells to format > Enter formula =ISBLANK(B3) or =B3="" > Click on Format > Fill tab > Select color > Click OK

1. Select the range in which you want to highlight blank cells.
Note: in this example we are selecting range B3:C9.

Select range in which to color blank cells

2. Select the Home tab.

3. Click on Conditional Formatting in the Style group.
4. Click on New Rules.

Select Conditional Formatting in Style group and click New Rules

5. Select Use a formula to determine which cells to format.
6. Enter the following formula =ISBLANK(B3).
7. Click on the Format button.
Note: it's important that the cell referenced in the ISBLANK formula refers to the active cell in the selection. In this example that cell is B3. If a cell in the selection is returning a result of ("") then the ISBLANK formula will not recognise this cell as a blank cell and will not highlight the cell. If you want to highlight such cells then you need to use the =B3="" formula, which will recognise such cells as blank.

Select Use a formula to determine which cells to format, enter formula and click format

8. Select the Fill tab.
9. Select a color.
10. Click OK.

Select the Fill tab, select color and click OK

11. Click OK.

Highlighted blank cells.

METHOD 1. Color blank cells using VBA

VBA

Sub Color_blank_cells()

'declare variables
Dim ws As Worksheet
Dim ColorRng As Range

Set ws = Worksheets("Analysis")
Set ColorRng = ws.Range("B3:C9")

'color blank cells
ColorRng.SpecialCells(xlCellTypeBlanks).Interior.Color = RGB(220, 230, 241)

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.

PREREQUISITES
Worksheet Names: Have a worksheet named Analysis.
Highlight Range: In this example the VBA code will highlight the blank cells in range ("B3:C9").

ADJUSTABLE PARAMETERS
Sheet Selection: Select the worksheet in which you want to highlight blank cells by changing the Analysis worksheet name in the VBA code.
Highlight Range: Select the range in which to highlight blank cells by changing the range ("B3:C9") in the VBA code.
Highlight Color: Select the color to highlight the blank cells by changing the RGB code (220, 230, 241) in the VBA code.

METHOD 2. Color blank cells with ("") recognised as blank cells using VBA

VBA

Sub Color_blank_cells()

'declare variables
Dim ws As Worksheet
Dim ColorRng As Range

Set ws = Worksheets("Analysis")
Set ColorRng = ws.Range("B3:C9")

'color blank cells
For Each ColorCell In ColorRng

If ColorCell = "" Then
ColorCell.Interior.Color = RGB(220, 230, 241)
Else
ColorCell.Interior.ColorIndex = xlNone
End If

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.

PREREQUISITES
Worksheet Names: Have a worksheet named Analysis.
Highlight Range: In this example the VBA code will highlight the blank cells in range ("B3:C9").

Explanation about how to color blank cells

EXPLANATION

EXPLANATION
This tutorial explains and provides step by step instructions on how to highlight blank cells using Excel and VBA methods.

Excel Method: This tutorial provides one Excel method that can be applied to highlight blank cells in a selected range by using the Conditional Formatting. This is accomplished in 11 steps.

VBA Methods: This tutorial provides two VBA methods that can be applied to highlight blank cells. The difference between the two methods is that the first method does not recognise a cell returning an output of "" as a blank cell, whilst the second method does.

Related Topic	Description	Related Topic and Description
Fill blank cells with a specific value	How to fill blank cells with a specific value using Excel and VBA methods
Count cells that are blank	How to count number of blank cells
Sum values if cells are blank	How to sum values if associated cells are blank
If a cell is blank	How to test if a cell is blank and return a specified value
Count cells that are not blank	How to count cells that are not blank