Looping through a range of cells

February 28th, 2007

Often times in Excel, you need to loop through the cells in a range and execute a specific action depending on the cell's value. Say for example you want to find negative numbers in a range and change the font color to red and make it bold. One way to do it is with conditional formats. Another way is to use the 'For Each...Next' loop structure in VBA. To keep this simple, let's say you wanted to do to a fixed range, A2:D4.

Loop Demo Picture 1

This code will loop through the cells in A2:D4 and format negative cells as bold red.

Sub LoopDemo()
    Dim c As Range

    For Each c In Range("A2:D4").Cells
        If c.Value < 0 Then
            c.Font.Bold = True
            c.Font.Color = vbRed
        End If
    Next c
End Sub

After the code execution, your range would look like this.

Loop Demo Picture 2

Posted in Excel, VBA | 1 Comment

1 Comment »

RSS feed for comments on this post. TrackBack URI

thank you!

Comment by stephan — March 18, 2009 #

Vergel’s Excel and Visual Basic Blog