Using Do...Loop Structures What do you do when you need to loop but you don't know in advance how many times to repeat the loop? This could happen if, for example, you want to loop only until a certain condition is met, such as encountering a blank cell in an Excel worksheet. The solution is to use a Do...Loop . The Do...Loop has four different syntaxes: | Do While condition [ statements ] Loop | Checks condition before entering the loop. Executes the statements only while condition is True. | | Do [ statements ] Loop While condition | Checks condition after running through the loop once. Executes the statements only while condition is True. Use this form when you want the loop to be processed at least once. | | Do Until condition [ statements ] Loop | Checks condition before entering the loop. Executes the statements only while condition is False. | | Do [ statements ] Loop Until condition | Checks condition after running through the loop once. Executes the statements only while condition is False. Again, use this form when you want the loop to be processed at least once. | Listing 6.11 shows a procedure called BigNumbers that runs down a worksheet column and changes the font color to magenta whenever a cell contains a number greater than or equal to 1,000. Listing 6.11. A Procedure That Uses a Do...Loop to Process Cells Until It Encounters a Blank Cell Sub BigNumbers() Dim rowNum As Integer, colNum As Integer, currCell As Range ' ' Initialize the row and column numbers ' rowNum = ActiveCell.Row colNum = ActiveCell.Column ' ' Get the first cell ' Set currCell = ActiveSheet.Cells(rowNum, colNum) ' ' Loop while the current cell isn't empty ' Do While currCell.Value <> "" ' ' Is it a number? ' If IsNumeric(currCell.Value) Then ' ' Is it a big number? ' If currCell.Value >= 1000 Then ' ' If so, color it magenta ' currCell.Font.Color = VBAColor("magenta") End If End If ' ' Increment the row number and get the next cell ' rowNum = rowNum + 1 Set currCell = ActiveSheet.Cells(rowNum, colNum) Loop End Sub The idea is to loop until the procedure encounters a blank cell. This is controlled by the following Do While statement: Do While currCell.Value <> "" currCell is an object variable that is set using the Cells method (which I describe in Chapter 8, "Programming Excel"). Next, the first If...Then uses the IsNumeric function to check whether the cell contains a number, and the second If...Then checks whether the number is greater than or equal to 1,000. If both conditions are True, the font color is set to magenta using the VBAColor function described earlier in this chapter. | 