For Each Loop in VBA

A For Each loop repeats VBA code for every item in a collection.

For example, if a range contains 10 cells, a For Each loop can process each of those 10 cells one by one.

Basic structure:

For Each item In collection
    statements
Next item

For Each is useful when you want to process objects without manually managing their position or index.

Common uses include:

• Processing every cell in a range.
• Processing every worksheet.
• Processing every workbook.
• Formatting selected cells.
• Checking values in a range.
• Processing rows or other Excel collections.

The basic syntax is:

For Each item In collection

    statements

Next item

The item represents the current object, while collection contains the objects being processed.

The following example processes every cell in a selected range.

Dim cell As Range

For Each cell In Range("A1:A5")

    MsgBox cell.Value

Next cell

Each cell from A1 to A5 is processed one by one.

The item variable represents the current object being processed.

Dim cell As Range

For Each cell In Range("A1:A5")

    MsgBox cell.Value

Next cell

Here, cell represents one Range object at a time.

A collection is a group of related objects.

Examples in Excel VBA include:

• Worksheets
• Workbooks
• Cells in a Range
• Rows
• Columns

For example:

For Each ws In Worksheets

Here, Worksheets is the collection.

One of the most common uses of For Each is processing cells in a range.

Dim cell As Range

For Each cell In Range("A1:A10")

    cell.Value = "Student"

Next cell

The word Student is written into every cell from A1 to A10.

For Each can read the value of every cell in a range.

Dim cell As Range

For Each cell In Range("A1:A5")

    MsgBox cell.Value

Next cell

The value of each cell is displayed.

You can change every cell in a collection using For Each.

Dim cell As Range

For Each cell In Range("A1:A5")

    cell.Value = UCase(cell.Value)

Next cell

This converts the text in each cell to uppercase.

For Each is very useful for formatting multiple cells.

Dim cell As Range

For Each cell In Range("A1:A10")

    cell.Font.Bold = True

Next cell

Every cell in the range becomes bold.

You can change the font size of every cell in a range.

Dim cell As Range

For Each cell In Range("A1:A10")

    cell.Font.Size = 14

Next cell

A For Each loop can contain an If statement.

Dim cell As Range

For Each cell In Range("A1:A10")

    If cell.Value >= 40 Then
        cell.Offset(0, 1).Value = "Pass"
    Else
        cell.Offset(0, 1).Value = "Fail"
    End If

Next cell

This checks every cell and writes the result in the next column.

You can process every worksheet in an Excel workbook.

Dim ws As Worksheet

For Each ws In Worksheets

    MsgBox ws.Name

Next ws

The name of every worksheet is displayed.

For Each can be used to change properties of multiple worksheets.

Dim ws As Worksheet

For Each ws In Worksheets

    ws.Tab.ColorIndex = 5

Next ws

This changes the tab color setting for each worksheet.

You can specify the workbook when processing its worksheets.

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

    MsgBox ws.Name

Next ws

This processes the worksheets belonging to the workbook containing the VBA code.

Rows can also be processed using For Each.

Dim rowItem As Range

For Each rowItem In Range("A1:C5").Rows

    rowItem.Font.Bold = True

Next rowItem

Each row in the specified range is processed.

Columns can be processed using For Each.

Dim colItem As Range

For Each colItem In Range("A1:E5").Columns

    colItem.Font.Bold = True

Next colItem

Each column in the range is processed.

For Each can be used to count cells containing a particular value.

Dim cell As Range
Dim count As Integer

count = 0

For Each cell In Range("A1:A10")

    If cell.Value <> "" Then
        count = count + 1
    End If

Next cell

MsgBox "Filled Cells = " & count

You can use For Each to search for a particular value.

Dim cell As Range

For Each cell In Range("A1:A10")

    If cell.Value = "Rahul" Then
        MsgBox "Rahul Found"
    End If

Next cell

The loop checks each cell in the range.

For Each can process student marks stored in a range.

Dim cell As Range

For Each cell In Range("B2:B11")

    If cell.Value >= 40 Then
        cell.Offset(0, 1).Value = "Pass"
    Else
        cell.Offset(0, 1).Value = "Fail"
    End If

Next cell

The result is written into the next column.

You can process every student name in a range.

Dim cell As Range

For Each cell In Range("A2:A11")

    cell.Value = UCase(cell.Value)

Next cell

This converts all student names to uppercase.

For Each can apply multiple formatting properties.

Dim cell As Range

For Each cell In Range("A1:C10")

    cell.Font.Bold = True
    cell.HorizontalAlignment = xlCenter

Next cell

Every cell in the range becomes bold and centered.

You can check whether a cell is empty.

Dim cell As Range

For Each cell In Range("A1:A10")

    If cell.Value = "" Then
        cell.Value = "Not Available"
    End If

Next cell

Empty cells are filled with Not Available.

For Each can process the cells returned by Excel's range methods. For example, you can work with cells containing formulas.

Dim cell As Range

For Each cell In Range("A1:C10")

    If cell.HasFormula Then
        cell.Font.Italic = True
    End If

Next cell

Cells containing formulas are made italic in this example.

The following example checks student marks and writes Pass or Fail beside each student.

Dim cell As Range

For Each cell In Range("B2:B11")

    If cell.Value >= 40 Then

        cell.Offset(0, 1).Value = "Pass"

    Else

        cell.Offset(0, 1).Value = "Fail"

    End If

Next cell

Here, column B contains marks and column C receives the result.

Beginners commonly make these mistakes:

• Forgetting Next.
• Using an inappropriate variable type for the object.
• Using the wrong collection.
• Trying to modify a collection while processing it without understanding the effect.
• Forgetting to specify the correct worksheet or range.
• Using a numeric variable when a Range or Worksheet object is required.

For example:

Dim cell As Range

For Each cell In Range("A1:A5")
    MsgBox cell.Value
Next cell

For Each can process all worksheets and display their names.

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

    MsgBox "Worksheet: " & ws.Name

Next ws

This is useful when working with workbooks containing multiple sheets.

• Use an appropriate object variable such as Range or Worksheet.
• Clearly specify the collection being processed.
• Use meaningful variable names.
• Keep the code inside the loop simple.
• Use If statements when you need to process only specific items.
• Test the loop with a small range before using it on large data.
• Use ThisWorkbook when you specifically want to work with the workbook containing the code.

A typical For Each workflow is:

1. Identify the collection.
2. Declare an object variable.
3. Start the For Each statement.
4. Process the current object.
5. Use Next to move to the next object.
6. Continue until every object has been processed.

Dim cell As Range

For Each cell In Range("A1:A5")

    cell.Font.Bold = True

Next cell

The For Each loop is an important VBA looping structure for working with collections of objects. Unlike a basic For...Next loop, it does not require you to manually use a numeric position to access each object.

It is especially useful for processing Excel ranges, cells, rows, columns, and worksheets.

Dim cell As Range

For Each cell In Range("B2:B11")

    If cell.Value >= 40 Then

        cell.Offset(0, 1).Value = "Pass"

    Else

        cell.Offset(0, 1).Value = "Fail"

    End If

Next cell

In this example, every cell in the marks range is processed automatically and the result is written beside each student's marks.

For Each becomes especially powerful when combined with conditions, formatting, calculations, and Excel objects in practical VBA projects.