To find and highlight the empty cells in an excel sheet using VBA.
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.
- Find the last columns and rows where a value is found
- Loop through column #1 to last used columns
- Loop through row #1 to last used rows
- Check if the cell is empty
- If found empty, then enter the text “Empty Cell” and change the color to blue
- Continue the loop for next iterations
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.
This approach is used for data validation or conditional formatting in excel using VBA.
Post you many like