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.
Highlight Blank Cells in Excel VBA: 6 Ways
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.
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.
- 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 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.
- 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 the selection using Excel VBA. Follow the steps given below to highlight the blank cells from the selection in your dataset.
Steps:
- In the beginning, go to the Developer tab >> click on Visual Basic.
- 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 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 by 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.
Read More: Excel VBA to Highlight Cell Based on Value
3. Using For Each Loop to Highlight Blank Cells
In the third method, we will show you how to use the For Each loop in Excel VBA 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.
- 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 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 by 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.
Read More: Excel Color Index Numbers
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.
- 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 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.
- 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 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.
- 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.
- 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 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: We used WorksheetFunction.CountA so it would be better to start the dataset from the A1 cell.
- Next, save the code by 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.
Download Practice Workbook
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. Thank you!
