SpecialCells - no cells selected
 

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.

SpecialCells - no cells selected
 

Try this Jim

Option Explicit

Sub test()
Dim rng As Range
Dim j As Integer

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"jims2994" wrote in message ...
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.