Color non blank cells

How to highlight non blank cells using Excel and VBA methods

METHOD 1. Color non blank cells using Conditional Formatting

EXCEL

Select range > Home tab > Style group > Click on Conditional Formatting > New Rules > Select Use a formula to determine which cells to format > Enter formula =NOT(ISBLANK(B3)) or =B3<>"" > Click on Format > Fill tab > Select color > Click OK

1. Select the range in which you want to highlight non blank cells.
Note: in this example we are selecting range B3:C9.

Select range in which to color blank cells

2. Select the Home tab.

3. Click on Conditional Formatting in the Style group.
4. Click on New Rules.

Select Conditional Formatting in Style group and click New Rules

5. Select Use a formula to determine which cells to format.
6. Enter the following formula =NOT(ISBLANK(B3)).
7. Click on the Format button.
Note: it's important that the cell referenced in the ISBLANK formula refers to the active cell in the selection. In this example that cell is B3. If a cell in the selection is returning a result of ("") then the ISBLANK formula will recognise this cell as a non blank cell and will highlight the cell. If you don't want to highlight such cells then you need to use the =B3<>"" formula, which will recognise such cells as blank.

Select Use a formula to determine which cells to format, enter formula and click format

8. Select the Fill tab.
9. Select a color.
10. Click OK.

Select the Fill tab, select color and click OK

10. Click OK.

Highlighted non blank cells.

METHOD 1. Color non blank cells using VBA

VBA

Sub Color_non_blank_cells()

'declare variables
Dim ws As Worksheet
Dim ColorRng As Range

Set ws = Worksheets("Analysis")
Set ColorRng = ws.Range("B3:C9")

'color non blank cells
On Error Resume Next
ColorRng.SpecialCells(xlCellTypeConstants).Interior.Color = RGB(220, 230, 241)
ColorRng.SpecialCells(xlCellTypeFormulas).Interior.Color = RGB(220, 230, 241)

End Sub

PREREQUISITES
Worksheet Names: Have a worksheet named Analysis.
Highlight Range: In this example the VBA code will highlight the non blank cells in range ("B3:C9").

ADJUSTABLE PARAMETERS
Sheet Selection: Select the worksheet in which you want to highlight non blank cells by changing the Analysis worksheet name in the VBA code.
Highlight Range: Select the range in which to highlight non blank cells by changing the range ("B3:C9") in the VBA code.
Highlight Color: Select the color to highlight the non blank cells by changing the RGB code (220, 230, 241) in the VBA code.

METHOD 2. Color non blank cells with ("") recognised as blank cells using VBA

VBA

Sub Color_non_blank_cells()

'declare variables
Dim ws As Worksheet
Dim ColorRng As Range

Set ws = Worksheets("Analysis")
Set ColorRng = ws.Range("B3:C9")

'color non blank cells
For Each ColorCell In ColorRng

If ColorCell <> "" Then
ColorCell.Interior.Color = RGB(220, 230, 241)
Else
ColorCell.Interior.ColorIndex = xlNone
End If

End Sub

PREREQUISITES
Worksheet Names: Have a worksheet named Analysis.
Highlight Range: In this example the VBA code will highlight the non blank cells in range ("B3:C9").

Explanation about how to color non blank cells

EXPLANATION

EXPLANATION
is tutorial explains and provides step by step instructions on how to highlight non blank cells using Excel and VBA methods.

Excel Method: This tutorial provides one Excel method that can be applied to highlight non blank cells in a selected range by using the Conditional Formatting. This is accomplished in 10 steps.

VBA Methods: This tutorial provides two VBA methods that can be applied to highlight non blank cells. The difference between the two methods is that the first method does not recognise a cell returning an output of "" as a blank cell, whilst the second method does.

Related Topic	Description	Related Topic and Description
Fill blank cells with a specific value	How to fill blank cells with a specific value using Excel and VBA methods
Count cells that are blank	How to count number of blank cells
Sum values if cells are blank	How to sum values if associated cells are blank
Color blank cells	How to highlight blank cells using Excel and VBA methods
Count cells that are not blank	How to count cells that are not blank