About Us

jims2994

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.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Multiple cells or columns are selected instead of selected cell or	Mikey	Excel Discussion (Misc queries)	1	April 29th 09 09:48 PM
Macro to take selected cells times a selected cell	Craig	Excel Programming	4	October 24th 05 12:54 AM
Excel03 - empty cells and SpecialCells ( xlVisible )	Chris Paterson	Excel Programming	2	August 23rd 05 02:35 PM
.Cells.SpecialCells(xlLastCell)	Simon Shaw	Excel Programming	8	May 5th 05 11:46 PM
How select/define cells with FIND method (maybe together with SpecialCells)	Marie J-son[_5_]	Excel Programming	2	December 14th 04 03:49 PM

Menu

About Us