How to Highlight All Non-Blank Cells in Excel

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.

Highlight All Non-Blank Cells 1

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

Method 1: Highlight All Non-Blank Cells by Conditional Formatting


Step 1: Select the table displayed in above screenshot.

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

Highlight All Non-Blank Cells 2

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

Highlight All Non-Blank Cells 3

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

Highlight All Non-Blank Cells 4

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

Highlight All Non-Blank Cells 5

Step 6: 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.

Highlight All Non-Blank Cells 6

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

Highlight All Non-Blank Cells 7

Verify that all non-blank cells are highlighted properly.

Highlight All Non-Blank Cells 8

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


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

Highlight All Non-Blank Cells 9

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

Highlight All Non-Blank Cells 10

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

Highlight All Non-Blank Cells 11

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

Highlight All Non-Blank Cells 12

Method 3: Highlight All Non-Blank Cells by VBA Code


Step 1: 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.

Step 2: 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

Step 3: Save code, quit Microsoft Visual Basic for Applications.

Step 4: Click Developer->Macros to run Macro.

Highlight All Non-Blank Cells 13

Step 5: Select ‘HighlightAllNonBlankCells’ and click Run.

Highlight All Non-Blank Cells 14

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

Highlight All Non-Blank Cells 15

              

 

 

You might also like:

Sidebar