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.
‘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)
‘In the next line of code we perform a conditional formatting!
If curCell.Value>=18000 Then curCell.Font.Bold=True
Now this process automates conditional formatting as shown in the training video!