SpecialCells - no cells selected
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. |
All times are GMT +1. The time now is 08:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com