Find and highlight the empty cells in an excel sheet using VBA

Find and highlight blank cells using vba

Objective

To find and highlight the empty cells in an excel sheet using VBA.

Approach

Herein the code checks each cell in each column. It goes through columns one be one. Wherever the cell is found as blank, it adds the text as “Empty Cell” in that cell and changes its color to blue.

Steps

  1. Find the last columns and rows where a value is found
  2. Loop through column #1 to last used columns
  3. Loop through row #1 to last used rows
  4. Check if the cell is empty
  5. If found empty, then enter the text “Empty Cell” and change the color to blue
  6. Continue the loop for next iterations

Code

Sub EmptyCells()
     Dim ColNum As Integer
    Dim RowNum As Integer
    Dim LastUsedRow As Integer
    Dim LastUsedColumn As Integer
     'Find the last used row and column
    LastUsedRow = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Row
    LastUsedColumn = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Column
        For ColNum = 1 To LastUsedColumn
    For RowNum = 2 To LastUsedRow
    'Check if the cell is empty,if yes, then add the text in cell and change the color
    If IsEmpty(Cells(RowNum, ColNum)) Then
          Cells(RowNum, ColNum).Value = "Empty Cell"
          Cells(RowNum, ColNum).Interior.Color = RGB(0, 255, 255)
    End If
    Next RowNum
    Next ColNum
End Sub

 

 In the image shown below, we can see that all those cell which were blank earlier, have the text now and their color is changed as blue.

Find and highlight blank cells using vba

 

Note

This approach is used for data validation or conditional formatting in excel using VBA.

Post you many like

Get data from ms access table using VBA Excel

1 comment on “Find and highlight the empty cells in an excel sheet using VBA

Comments are closed.