Highlight Cells with Text or Formulas (non-empty cells)

Add to Favorites

This macro will highlight all cells in excel which are not empty. This means that if a cell contains formulas, text, numbers, or other characters it will be filled in with color, or highlighted. The first macro listed will work on the entire active sheet which you are on. The second macro will be limited to a predefined or specified range in the worksheet; this range is determined within the macro and can be changed by changing the cell references on this line of code: For Each Rng In Range("A1:B25").

When you run this macro, it will overwrite all cells with color which currently contain data. This means that if you have a colorful worksheet and you run this macro, it will replace this color with the color specified in the macro.

This macro works best when you just want to highlight everything in a worksheet so you can more easily locate information. This is particularly useful if someone has hidden data by making the text the same color as the backgroun; using this macro in that case will color the background of all previously seemingly empty cells.

To change the color of the highlight for the first macro, change the number in this line of code If r.Value <> "" Then r.Interior.ColorIndex = 3 'red.

To change the color of the highlight for the second macro, change the number in this line of code Rng.Interior.ColorIndex = 3 'red.

Where to install the macro:  Module

Works on the entire active worksheet

Sub Highlight_Cells_With_Text_or_Formulas()
 'Highlights all cells with text or formulas on the active sheet
 'Will remove color from cells without formulas or text

Dim r As Range

With ActiveSheet.UsedRange
    .Interior.ColorIndex = xlNone
    For Each r In .Cells
        If r.Value <> "" Then r.Interior.ColorIndex = 3 'red
    Next
End With End Sub

[/CODE]

Works on predefined range of cells.

Sub Highlight_Cells_With_Text_or_Formulas_Range()

'Highlights all cells with text or formulas on the active sheet
'Will remove color from cells without formulas or text

Dim Rng As Range

For Each Rng In Range("A1:B25") 'Range to highlight cells
If Rng.Value <> "" Then
Rng.Interior.ColorIndex = 3 'red
Else
Rng.Interior.ColorIndex = 0 'blank End If
Next Rng End Sub





Similar Content on TeachExcel
Highlight Cells which Contain Formulas
Macro: This macro will highlight all of the cells in a worksheet which contain a formula. The fir...
Format Cells as Text in Excel
Macro: This free Excel macro formats a selection of cells as Text in Excel. This macro appli...
Add Comments to Cells with an Excel Macro
Macro: Add comments to cells in Excel with this macro. This allows you to quickly and easily add ...
Apply Conditional Formatting to Multiple Cells with a Single Formula
Tutorial: How to use a single formula to apply conditional formatting to multiple cells at once in E...
Prevent Errors From Appearing in Excel
Tutorial: How to prevent errors from appearing in formulas in Excel. This is especially helpful for ...
How to Arrange Data within Cells in Excel
Tutorial: In this tutorial I am going to look at cell alignment / arrangement. These features allow ...



How to Install the Macro
  1. Select and copy the text from within the grey box above.

  2. Open the Microsoft Excel file in which you would like the Macro to function.

  3. Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.  Or For other ways to get there, Click Here.

  4. On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.

  5. If the Macro goes in a Module, Click Here, otherwise continue to Step 8.

  6. If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.

  7. If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.

  8. Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.

  9. You are now ready to run the macro.


Similar Content
Highlight Cells which Contain Formulas
Macro: This macro will highlight all of the cells in a worksheet which contain a formula. The fir...
Format Cells as Text in Excel
Macro: This free Excel macro formats a selection of cells as Text in Excel. This macro appli...
Add Comments to Cells with an Excel Macro
Macro: Add comments to cells in Excel with this macro. This allows you to quickly and easily add ...
Excel Forum