Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SpecialCells - no cells selected | Excel Programming | |||
SpecialCells - no cells selected | Excel Programming | |||
Excel03 - empty cells and SpecialCells ( xlVisible ) | Excel Programming | |||
.Cells.SpecialCells(xlLastCell) | Excel Programming | |||
How select/define cells with FIND method (maybe together with SpecialCells) | Excel Programming |