For Next Loop

A ‘for next loop’ macro to calculate and format cells in Excel
Let’s say you have a range of cells like names of employees and their corresponding salaries. Now you wish to anlyze the data in the range automatically. How do do this? By using the ‘looping’ features of VBA.
One way to loop through a range is to use the For…Next loop with the Cells property. Using the Cells property, you can substitute the loop counter (or other variables or expressions) for the cell index numbers. In the following example, the variable counter is substituted for the row index. The procedure loops through the range C2:C10, setting to bold any value that is greater than or equal to 18000.
Macro Code:
Sub DataBold()
‘Our data starts at row 2. The first row has only headers
For Counter=2 To 10
‘Our salary data is is in column 3 or C and our first salary cell is in row 2 and column 3 (C2)
Set curCell=Worksheets(“Sheet1”).Cells(Counter,3)
‘In the next line of code we perform a conditional formatting!
If curCell.Value>=18000 Then curCell.Font.Bold=True
Next Counter
End Sub
Now this process automates conditional formatting as shown in the training video!

Further reading:
VBA loops

One thought on “For Next Loop

  1. M. Ea

    Before I phrase my question, I wish to THANK YOU so much ! for your generosity in posting these WONDERFUL short lessons! And to say that you are a very good teacher!
    In order to avoid the user’s having to stare at this complicated formula when clicking on the worksheet cells, I wish to somehow use a For loop to substitute for the formula:
    Sub Macro04_ListRecommendersInOneColumn()
    ‘Macro recorded 7/5/2014 by Thinkpad11
    ‘Activity 1
    ‘In the “Recomm.” column, D2 will be the first cell to get data; insert “Recommenders” names in a vertical list
    ActiveCell.Formula = “=IF(AND(AT2=””””,AS2=””””,AR2=””””,AQ2=””””,AP2=””””),AO2,” & _
    “IF(AND(AT2=””””,AS2=””””,AR2=””””,AQ2=””””),AO2&CHAR(10)&AP2,” & _
    “IF(AND(AT2=””””,AS2=””””,AR2=””””),AO2&CHAR(10)&AP2&CHAR(10)&AQ2,” & _
    “IF(AND(AT2=””””,AS2=””””),AO2&CHAR(10)&AP2&CHAR(10)&AQ2&CHAR(10)&AR2,” & _

    ‘Activity 2
    ‘Format the column so all Recommenders for each applicant will be visible
    With Selection
    .WrapText = True
    .VerticalAlignment = xlTop
    End With

    ‘Activity 3
    ‘Autofill the rest of the column with all the other applicants’ Recommenders
    Selection.AutoFill Destination:=Range(“D2:D” & lngLastRecord), Type:=xlFillDefault

    ‘Activity 4
    ‘Widen the column enough to accommodate Recommenders with long names
    End Sub

    Any help you can give would be appreciated!


Leave a Reply

Your email address will not be published. Required fields are marked *