That works. Thanks.

"Vergel Adriano" wrote:

Hi,

You don't need to select the sheet and range. You can keep the On Error
Resume Next, but assign the result of SpecialCells to a range variable that
you set to Nothing each time. The code below will work and highlight all
cells with a formula.


Sub test()

Dim j As Integer
Dim rng As Range

On Error Resume Next

For j = 1 To Sheets.Count
Set rng = Nothing
Set rng = Sheets(j).UsedRange.SpecialCells(xlCellTypeFormula s, 23)
If Not rng Is Nothing Then
With rng.Interior
.ColorIndex = 36
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If
Next j


End Sub



--
Hope that helps.

Vergel Adriano


"jims2994" wrote:

I'm trying to cycle through every tab in a workbook and change the color of
all cells that contain a formula. The macro I have works until I encounter a
tab that does not have any cells with a formula. How can I check to see if
no cells are returned?

Here's what I have so far:

For j = 1 To Sheets.Count

Sheets(j).Select
On Error Resume Next
Selection.SpecialCells(xlCellTypeFormulas, 23).Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

Next j

Without the "On Error Resume Next" line, the macro errors out when it
encounters a tab without any formulas. With it in there, the active cell in
tabs without formulas changes to color 36, which isn't quite what I want to
have happen.