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.
Prepare a table with some cells are blank.
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.
Step 3: Click the arrow on Conditional Formatting icon, select New Rule.
Step 4: In ‘New Formatting Rule’ dialog ‘Select a Rule Type’ pane, select ‘Use a formula to determine which cells to format’ option.
Step 5: Enter formula =NOT(ISBLANK(A1)) into ‘Format values where this formula is true.’.
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.
Step 7: 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.
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.
Step 2: On Go To Special dialog, check on Constants, then Numbers, Text, Logicals and Errors are activated by default. Click OK.
Step 3: 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.
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.
Step 5: 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.