Microsoft Office Tutorials and References
In Depth Information
Working with Ranges
Figure 11-7: A VBA procedure analyzes the currently selected range.
Looping through a selected range efficiently
A common task is to create a macro that evaluates each cell in a range and performs an
operation if the cell meets a certain criterion. The procedure that follows is an example of such a
macro. The ColorNegative procedure sets the cell’s background color to red for cells that
contain a negative value. For non-negative value cells, it sets the background color to none.
This example is for educational purposes only. Using Excel’s conditional formatting is a
much better approach.
‘ Makes negative cells red
Dim cell As Range
If TypeName(Selection) <> “Range” Then Exit Sub
Application.ScreenUpdating = False
For Each cell In Selection
If cell.Value < 0 Then
cell.Interior.Color = RGB(255, 0, 0)
cell.Interior.Color = xlNone