Automated Calculations Formatting VBA

Automated calculation and formatting Excel macro: Do While Loop
Looping through a range of Excel worksheet cells and performing actions like calculations and formatting depending on defined conditions can truly be called automation. We already learnt abot the for next loop. Today we’ll learn how to use another powerful looping method called do while. In the for next loop we need to know our starting and end points. With the help of the do while loop we can define when the looping starts and when it ends so this method is more flexible.
Also while we are looping we can use the IF method to perform more interesting actions like conditional formatting as you’ll see in the training video below:

 

Try out this Macro Code also:
Sub MyConditions()
‘define the starting point of calculations
‘row second
Row=2
‘start the loop and continue till the cell B2 is not empty
Do While Cells(Row,2)<>””
Cells(Row,3).Value=Cells(Row,2)*0.3
Cells(Row,4).Value=Cells(Row,2)*0.1
Cells(Row,5).Value=Cells(Row,2).Value+Cells(Row,3).Value+Cells(Row,4).Value
‘format the cells based on specified conditions
If Cells(Row,5).Value>=8000 Then
Cells(Row,5).Font.Bold=True
Cells(Row,5).Font.ColorIndex=3
ElseIf Cells(Row,5).Value>=7000 Then
Cells(Row,5).Font.ColorIndex=4
ElseIf Cells(Row,5).Value>=3000 Then
Cells(Row,5).Font.ColorIndex=5
End If
‘go to next row i. e. 3
‘now loop again and perform the actions if cell B3 is not empty and so on…
Row=Row+1
Loop
End Sub
Further reading
Geek School: Learn How to Use Excel Macros to Automate Tedious Tasks

Leave a Reply

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