Microsoft Office Tutorials and References
In Depth Information
Working with Ranges
The ColorNegative procedure certainly works, but it has a serious flaw. For example, what if
the used area on the worksheet were small, but the user selects an entire column? Or ten
columns? Or the entire worksheet? You don’t need to process all those empty cells, and the user
would probably give up long before your code churns through all those cells.
A better solution ( ColorNegative2 ) follows. In this revised procedure, I create a Range object
variable, WorkRange , which consists of the intersection of the selected range and the
worksheet’s used range. Figure 11-8 shows an example; the entire column D is selected (1,048,576
cells). The worksheet’s used range, however, consists of the range B2:I16. Therefore, the
intersection of these ranges is D2:D16, which is a much smaller range than the original selection. The time
difference between processing 15 cells versus processing 1,048,576 cells is significant.
Figure 11-8: Using the intersection of the used range and the selected ranged results in fewer cells to process.
‘ Makes negative cells red
Dim WorkRange As Range
Dim cell As Range
If TypeName(Selection) <> “Range” Then Exit Sub
Application.ScreenUpdating = False
Set WorkRange = Application.Intersect(Selection, _
For Each cell In WorkRange
If cell.Value < 0 Then
cell.Interior.Color = RGB(255, 0, 0)
cell.Interior.Color = xlNone