Free Microsoft Excel Training Videos

A 'for 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
For loop macro