How to Loop in Excel

Rate: (0 Ratings)

Macros are used to perform repetitive actions over and over automatically. A loop is an effortless and powerful macro. A cell or range of cells reference on a worksheet tells Excel where to look for the values or data you want to use in a formula. You can use the data that is contained in the different parts of a worksheet in a formula. You can also use the value from one cell in several formulas. You would have reasons wanting to loop through range of cells, such as to change cell color based on a condition, to create sets of data and to generate reports. There are multiple ways to loop through cell ranges. Here is the easiest way to loop, which is For Each Loop.

Flag Article

Instructions

Difficulty: Moderate

Step1

Create a new workbook in Microsoft Excel.

Step2

Go to Tools > Macro > Visual Basic Editor, or press Alt + F11 to open the Visual Basic Editor.

Step3

Go to Insert > Module. A blank module window will appear inside the main window of the Visual Basic Editor.

Step4

Give your macro a name. Type:Sub My Macro ()End Sub

Step5

Add the code below between the Sub line and the End Sub lines.----------------------------------------------------For Each MyCell in Selection If MyCell.Value Like “OK” Then MyCell.Font.Bold = TrueEnd IfNext----------------------------------------------------This means A For Each… Next Loop makes the word “OK” darker than other text, everywhere in a selection.The total code will be:Sub My Macro ()For Each MyCell in Selection If MyCell.Value Like “OK” Then MyCell.Font.Bold = TrueEnd IfNextEnd Sub

Step6

Go to Tools > Macro > Record New Macro to record the macro you just created.

Step7

Edit the code by going to Tools > Macro > Macros to find your macro. Select it and click Edit to see the code.