Suppose we have a table with some blank cells, if we want to highlight all non-blank cells how can we do? Though we can press ctrl and pick each non-blank cell one by one, this way is very bothersome. We need a convenient way to highlight all non-blank cells immediately. Actually, we can implement this via excel Conditional Formatting function, we can edit a new rule to filter all non-blank cells and then highlight them. We can also implement this via Go To Special function by checking on proper options. If you are familiar with VBA macro, you can also edit VBA code. This article will show you these three methods details step by step. Please read this article below and pick one of them to help you to solve your problem.

Precondition:

Prepare a table with some cells are blank.

We can highlight all non-blank cells via below two methods.

1: Highlight All Non-Blank Cells by Conditional Formatting

Step1: Select the table displayed in above screenshot.

Step2: Click Home in ribbon, click Conditional Formatting in Styles group.

Step3: Click the arrow on Conditional Formatting icon, select New Rule.

Step4: In ‘New Formatting Rule’ dialog ‘Select a Rule Type’ pane, select ‘Use a formula to determine which cells to format’ option.

Step5: Enter formula =NOT(ISBLANK(A1)) into ‘Format values where this formula is true.’.

Step6: Click Format button in Edit the Rule Description pane. On Format Cells, click Fill tab, select background color, then click OK to quit current dialog.

Step7: In Preview field, you can see that cell is highlighted with yellow. Click OK to quit editing.

Verify that all non-blank cells are highlighted properly.

2: Highlight All Non-Blank Cells by Go To Special

Step1: Select the table, click F5 to load Go To dialog. Click Special button.

Step2: On Go To Special dialog, check on Constants, then Numbers, Text, Logicals and Errors are activated by default. Click OK.

Step3: After step#2, all non-blank cells are selected.

In Home ribbon, click Fill Color arrow in Font group. Select color, then non-blank cells are filled with this color.

3: Highlight All Non-Blank Cells by VBA Code

Step1: On current visible worksheet, right click on sheet name tab to load Sheet management menu. Select View Code, Microsoft Visual Basic for Applications window pops up.

Or you can enter Microsoft Visual Basic for Applications window via Developer->Visual Basic. You can also press Alt + F11 keys simultaneously to open it.

Step2: In Microsoft Visual Basic for Applications window, enter below code:

Sub HighlightAllNonBlankCells()
    
    Dim myRange As Range
    Dim myCell As Range
    Dim nonEmptyCells As Range
    Set myRange = Application.ActiveSheet.UsedRange
    
    For Each myCell In myRange
        If Not (myCell.Value = "") Then
           If nonEmptyCells Is Nothing Then
              Set nonEmptyCells = myCell
           Else
              Set nonEmptyCells = Application.Union(nonEmptyCells, myCell)
           End If
        End If
    Next
    
    If Not (nonEmptyCells Is Nothing) Then
       nonEmptyCells.Select
    End If
    
End Sub

Step3: Save code, quit Microsoft Visual Basic for Applications.

Step4: Click Developer->Macros to run Macro.

Step5: Select ‘HighlightAllNonBlankCells’ and click Run.

Verify that all non-blank cells are selected. Then you can follow step#3 in method#2 to fill highlight color.

4. Video: Highlight All Non-Blank Cells

This Excel video tutorial where we’ll unravel the secrets of highlighting all non-blank cells. Let’s dive into the first method using the “Conditional Formatting” feature, followed by the “Go To Special” feature, and finally, a method employing a VBA macro.

https://youtu.be/zG0TfaiNLBc