Excel Guides

Progression Indicator in a Macro in Excel

A progression indicator is a great way to keep track of your progress when working on a long or difficult project in Excel. By creating a simple macro, you can have an automatically updating progress bar that will let you know how far along you are in your work. This can be a helpful way to stay motivated and on track.

To create a progression indicator, first open up the Visual Basic Editor by going to Tools > Macro > Visual Basic Editor. Then, in the editor window, click Insert > Module. This will insert a new module into your workbook.

In the module, paste the following code:

Sub ProgressBar() 
 Dim i As Integer 
 For i = 1 To 100 
     Cells(i, 1).Value = i 
     Cells(i, 1).Select 
     With Selection.Interior 
         .Pattern = xlSolid 
         .PatternColorIndex = xlAutomatic 
         .Color = RGB(255, 255, 0) 
         .TintAndShade = 0 
         .PatternTintAndShade = 0 
     End With 
 Next i 
End Sub

This code will create a column of cells filled with numbers from 1 to 100. Each cell will be highlighted yellow to indicate that it contains part of the progress bar. You can change the color of the cells by editing the RGB values in the code.

Once you have the code in place, run the macro by going to Run > Run Sub/UserForm. Your progress bar should now appear in your workbook!

To use your progress indicator, simply update the number in each cell as you complete parts of your project. For example, if you've completed 10% of your project, change the number in cell 1 from 1 to 10. As you continue to make progress, the cells will fill up with color, giving you a visual representation of how far along you are.

You can also add conditional formatting to your progress cells so that they change color as you reach certain milestones. For example, you could have the cells turn green when you reach 50% complete, and red when you reach 100%. To do this, select all of the cells in your progress column and then go to Home > Conditional Formatting > New Rule.

In the 'Select a Rule Type' window that appears, choose 'Format only cells that contain'.

In the next window, set up your rule as follows:

Click 'Format', and then choose your desired fill color in the 'Fill' tab.

Click 'OK' twice to close out of the dialog boxes and apply your conditional formatting.

Your progress cells should now change color based on their value! You can add additional rules for different milestone percentages if desired.

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.