Microsoft Office Tutorials and References
In Depth Information
Working with Ranges
The ColorNegative2 procedure is an improvement, but it’s still not as efficient as it could be
because it processes empty cells. A third revision, ColorNegative3 , is quite a bit longer, but
it’s much more efficient. I use the SpecialCells method to generate two subsets of the
selection: One subset ( ConstantCells ) includes only the cells with numeric constants; the other
subset ( FormulaCells ) includes only the cells with numeric formulas. The code processes the
cells in these subsets by using two For Each-Next constructs. The net effect: Only nonblank,
nontext cells are evaluated, thus speeding up the macro considerably.
Sub ColorNegative3()
‘ Makes negative cells red
Dim FormulaCells As Range, ConstantCells As Range
Dim cell As Range
If TypeName(Selection) <> “Range” Then Exit Sub
Application.ScreenUpdating = False
‘ Create subsets of original selection
On Error Resume Next
Set FormulaCells = Selection.SpecialCells(xlFormulas, xlNumbers)
Set ConstantCells = Selection.SpecialCells(xlConstants, xlNumbers)
On Error GoTo 0
‘ Process the formula cells
If Not FormulaCells Is Nothing Then
For Each cell In FormulaCells
If cell.Value < 0 Then
cell.Interior.Color = RGB(255, 0, 0)
Else
cell.Interior.Color = xlNone
End If
Next cell
End If
‘ Process the constant cells
If Not ConstantCells Is Nothing Then
For Each cell In ConstantCells
If cell.Value < 0 Then
cell.Interior.Color = RGB(255, 0, 0)
Else
cell.Interior.Color = xlNone
End If
Next cell
End If
End Sub
The On Error statement is necessary because the SpecialCells method generates
an error if no cells qualify.
A workbook that contains the three ColorNegative procedures is available on the
companion CD-ROM. The file is named efficient looping.xlsm .
Search JabSto ::




Custom Search