How to Highlight Blank Cells in Excel VBA (6 Suitable Examples)

Looking for ways to know how to highlight blank cells in Excel VBA? Sometimes, we want to highlight the cells which contain no data or empty strings. We can do it by using VBA. Here, you will find 6 ways to highlight blank cells in Excel VBA.


Download Practice Workbook


6 Ways to Highlight Blank Cells in Excel VBA

Here, we have a dataset containing the Product name and Sales in January, February, and March of a shop. Now we will use this dataset to show you how to highlight blank cells in Excel VBA.

Ways to Highlight Blank Cells in Excel VBA


1. Using VBA SpecialCells Method to Highlight Blank Cells in Excel

In the first method, we will show you how to use the SpecialCells Method in Excel VBA to highlight blank cells. Go through the steps given below to do it on your dataset.

Steps:

  • Firstly, go to the Developer tab >> click on Visual Basic.

Using VBA SpecialCells Method to Highlight Blank Cells in Excel

  • Now, Microsoft Visual Basic for Application box will open.
  • Then, click on Insert >> select Module.

  • After that, write the following code in your Module.
Sub Using_SpecialCells_Method()
Dim SpecialCells As Worksheet
Dim Sales As Range
Set SpecialCells = Worksheets("Using SpecialCells Method")
Set Sales = SpecialCells.Range("B5:E10")
Sales.SpecialCells(xlCellTypeBlanks).Interior.Color = RGB(255, 0, 0)
End Sub

Code for Using VBA SpecialCells Method to Highlight Blank Cells in Excel

Code Breakdown

  • Firstly, we created a Sub Procedure as Using_SpecialCells_Method.
  • Then, we declared SpecialCells as Worksheet and Sales as Range.
  • Next, we set the “Using specialCells Method” worksheet as SpecialCells.
  • After that, we set Cell range B5:E10 as Sales from SpecialCells using the Range method.
  • Finally, we change the color of blank cells in the Sales range from the SpecialCells worksheet using the Interior.Color property.
  • Next, click on the Save button and go back to the worksheet.

  • Afterward, go to the Developer tab >> click on Macros.

  • Now, the Macro box will appear.
  • Then, select Using_SpecialCells_Method.
  • After that, click on Run.

  • Finally, you will see that all the blank cells have been highlighted using the SpecialCells Method in Excel VBA.


2. Highlighting Blank Cells Using VBA Selection Property

We can also highlight blank cells from selection using Excel VBA. Follow the steps given below to highlight the blank cells from selection in your dataset.

Steps:

  • In the beginning, go to the Developer tab >> click on Visual Basic.

Highlighting Blank Cells Using VBA Selection Property

  • Then, insert a module going through the step shown in Method 1.
  • After that, write the following code in your Module.
Sub HighlightBlankCells_Using_Selection()
Dim Sales As Range
Set Sales = Selection
Sales.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed
End Sub

Code for Highlighting Blank Cells Using VBA Selection Property

Code Breakdown

  • Firstly, we created a Sub Procedure as HighlightBlankCells_Using_Selection.
  • Then, we declared Sales as Range.
  • After that, we used the Selection property to select any range from the dataset. 
  • Finally, we change the color of blank cells in the Sales range using the Interior.Color property.
  • Next, save the code going through the same steps shown in Method 1.
  • Afterward, select Cell range B5:E10.
  • Now, go to the Developer tab >> click on Macros.

  • Then, the Macro box will appear.
  • Next, select HighlightBlankCells_Using_Selection.
  • After that, click on Run.

  • Finally, you will see that all the blank cells have been highlighted from Selection.


3. Using For Each Loop to Highlight Blank Cells

In the third method, we will show you how to use the For Each loop to highlight blank cells in Excel. The For Each loop is used to execute repetitive actions for a particular range. Go through the steps given below to do it on your own

Steps:

  • Firstly, go to the Developer tab >> click on Visual Basic.

Using For Each Loop to Highlight Blank Cells

  • Then, insert a module going through the step shown in Method 1.
  • Now, write the following code in your Module.
Sub Using_For_Each_Loop()
  Dim Sales As Range
  Set Sales = Selection
  For Each Cell_Value In Sales
    If Cell_Value.Text = "" Then
       Cell_Value.Interior.Color = RGB(255, 181, 106)
    Else
       Cell_Value.Interior.ColorIndex = xlNone
    End If
  Next
End Sub

Code for Using For Each Loop to Highlight Blank Cells

Code Breakdown

  • Firstly, we created a Sub Procedure as Using_For_Each_Loop.
  • Then, we declared Sales as Range.
  • After that, we used the Selection property to select any range from the dataset. 
  • Next, we used the For Each loop for Cell_Value in the Sales range.
  • Afterward, we used the If statement to check if Cell_Value is equal to an empty string using the Text function.
  • Finally, if the statement is TRUE then the color of blank cells in the Sales range will be changed using the Interior.Color property otherwise no will be applied to that cells.
  • Next, save the code going through the same steps shown in Method 1.
  • After that, select Cell range B5:E10.
  • Then, go to the Developer tab >> click on Macros.

  • Now, the Macro box will appear.
  • Next, select Using_For_Each_Loop.
  • After that, click on Run.

  • Finally, you will be able to highlight all the blank cells using the For Each loop.

Highlighting Blank Cells in Excel VBA Using For Each Loop


4. Highlight Empty Strings as Blank Using VBA

Now, we will show you how to highlight empty strings as blank in Excel VBA. Follow the steps given below to do it on your own.

Steps:

  • In the beginning, go to the Developer tab >> click on Visual Basic.

Highlight Empty Strings as Blank Using VBA

  • Then, insert a module going through the step shown in Method 1.
  • Now, write the following code in your Module.
Sub Empty_Strings_as_Blank()
Dim SpecialCells As Worksheet
Dim Sales As Range
Set SpecialCells = Worksheets("Empty Strings as Blank")
Set Sales = SpecialCells.Range("B5:E10")
For Each Cell_Value In Sales
    If Cell_Value.Text = "" Then
       Cell_Value.Interior.Color = RGB(255, 181, 106)
    Else
       Cell_Value.Interior.ColorIndex = xlNone
    End If
  Next
End Sub

Code for Highlight Empty Strings as Blank Using VBA

Code Breakdown

  • Firstly, we created a Sub Procedure as Empty_Strings_as_Blank.
  • Then, we declared SpecialCells as Worksheet and Sales as Range.
  • Next, we set the “Empty Strings as Blank” worksheet as SpecialCells.
  • After that, we set Cell range B5:E10 as Sales from SpecialCells using the Range method.
  • Then, we used the For Each loop for Cell_Value in the Sales range.
  • Afterward, we used the If statement to check if Cell_Value is equal to an empty string using the Text function.
  • Finally, if the statement is TRUE then the color of blank cells in the Sales range will change using the Interior.Color property else the color is selected as None.
  • Next, save the code going through the same steps shown in Method 1.
  • Then, go to the Developer tab >> click on Macros.

  • Now, the Macro box will appear.
  • After that, select Empty_Strings_as_Blank.
  • Next, click on Run.

  • Finally, you will be able to highlight all the empty strings as blank cells using the For Each loop.


5. Use of Command Button to Highlight Blank Cells in Excel

We can also highlight blank cells using the Command Button in Excel. The Command Button is used to execute a procedure that is in that worksheet. Go through the steps given below to do it on your own dataset.

Steps:

  • Firstly, go to the Developer tab >> click on Insert >> select Command Button from ActiveX Controls.

Use of Command Button to Highlight Blank Cells in Excel

  • Then, insert a Command Button and Right-click on it.

  • Next, click on CommandButton Object >> select Edit.

  • After that, type Highlight Blank Cells as the name of the Command Button.

  • Again, select the Command Button and Right-click on it.
  • Then, click on View Code.

  • Now, write the following code in your Command Button.
Private Sub CommandButton1_Click()
For Row = 5 To 10
    For Column = 2 To 5
    If Worksheets("Use of Command Button").Cells(Row, Column).Value = "" Then
    Worksheets("Use of Command Button").Cells(Row, Column).Interior.ColorIndex = 4
    End If
    Next
Next
End Sub

Code for Using Command Button to Highlight Blank Cells in Excel

Code Breakdown

  • Firstly, in the Private Sub, we used a For loop for Rows 5 to 10.
  • Then, we used another For loop for Columns 2 to 5.
  • Next, we used the If statement to check if the cell in the “Use of Command  Button” worksheet is equal to blank.
  • Finally, if the statement is TRUE then the color of blank cells in the Sales range will change using the Interior.Color property.
  • Next, click on the Design Mode Feature.
  • After that, click on the Command Button.

Clicking on Command Button to Use Command Button to Highlight Blank Cells in Excel

  • Finally, you will be able to highlight all the empty strings as blank cells using the Command Button.


6. Using WorksheetFunction in Excel to Highlight Blank Cells

In the final method, we will show you how to use the WorksheetFunction to highlight blank cells in Excel. You can use this method if your dataset starts with Cell A1.

Follow the steps given below to do it on your own dataset.

Steps:

  • In the beginning, go to the Developer tab >> click on Visual Basic.

Using WorksheetFunction in Excel to Highlight Blank Cells

  • Then, insert a module going through the step shown in Method 1.
  • Now, write the following code in your Module.
Sub Using_Worksheet_Function()
Dim Row_Number As Long
Dim Column_Number As Long
Dim Product As Range
Dim Sales As Range
ActiveSheet.Select
    Row_Number = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlDown)))
    Column_Number = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlToRight)))
Set Product = Range(Cells(1, 1), Cells(Row_Number, Column_Number))
For Each Sales In Product.Cells
    If Sales = "" Then
    Sales.Interior.Color = RGB(255, 0, 0)
    End If
Next Sales
End Sub

Code for Using WorksheetFunction in Excel to Highlight Blank Cells

Code Breakdown

  • Firstly, we created a Sub Procedure called Using_Worksheet_Function.
  • Then, we declared Row_Number and Column_Number as Long, Product and Sales as Range.
  • Next, we select the ActiveSheet using the Select method.
  • After that, we count the number of rows using the COUNTA function and set it as Row_Number.
  • Similarly,  we count the number of columns using the COUNTA function and set it as Column_Number.
  • Then, we set the Product as a range from Cell(1,1) to Cell(Row_Number, Column_Number).
  • Next, we used the For Each loop for Sales in the Product range.
  • Afterward, we used the If statement to check if Sales is equal to an empty string.
  • Finally, if the statement is TRUE then the color of blank cells in the Sales range will change using the Interior.Color property.

Note: As we used WorksheetFunction.CountA so it would be better to start the dataset from the A1 cell.

  • Next, save the code going through the same steps shown in Method 1.
  • Then, go to the Developer tab >> click on Macros.

  • Now, the Macro box will appear.
  • After that, select Using_Worksheet_Function.
  • Next, click on Run.

  • Finally, you will be able to highlight all the empty strings as blank cells using the WorksheetFunction in Excel.


Practice Section

This section gives you the dataset to practice on your own and learn to use these methods.

Practice Section


Conclusion

So, in this article, you will find 6 ways to highlight blank cells in Excel VBA. Use any of these ways to accomplish the result in this regard. Hope you find this article helpful and informative. Feel free to comment if something seems difficult to understand. Let us know any other approaches which we might have missed here. And, visit ExcelDemy for many more articles like this. Thank you!

Arin

Arin

Hello, I'm Arin. I graduated from Khulna University of Engineering and Technology (KUET) from the Department of Civil Engineering. I am passionate about learning new things and increasing my data analysis knowledge as well as critical thinking. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo