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

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

---