When writing Excel VBA macros, one of the most powerful and elegant techniques you can learn is how to use “For Each” loops to work with cell ranges.

Unlike traditional For i = 1 To n loops that use numerical indexes, the For Each loop allows you to directly iterate over objects — such as cells, worksheets, workbooks, or even shapes — without worrying about row or column numbers.

In this complete guide, you’ll learn how to use For Each to manipulate cell ranges efficiently, safely, and professionally.
We’ll explore practical examples such as highlighting cells, copying values, applying conditions, and performing advanced operations — all while keeping your VBA code simple and readable.

---

✅ What Is the “For Each” Loop in VBA?

・Definition and purpose

The For Each loop is designed to repeat a set of commands for each object in a specified collection.

The general syntax is:

For Each element In collection
'Your code here
Next element

It’s ideal when you want to:

• Process every cell in a range
• Loop through worksheets, rows, or columns
• Handle objects dynamically without using index numbers

---

・How it differs from For…Next

Feature	For Each	For…Next
Iterates over	Objects (e.g., Cells, Sheets)	Index numbers
Speed	Slightly slower for large ranges	Faster for numeric loops
Readability	Higher — no counting logic	Requires manual index handling
Typical use	Cells, worksheets, collections	Fixed ranges or numeric control

When dealing with Range objects, For Each is the most natural and clear choice.

---

✅ Basic Example: Loop Through a Range

・Loop through a simple range

Sub BasicForEachLoop()
Dim c As Range
For Each c In Range("A1:A5")
c.Value = "Test"
Next c
End Sub

✅ This code writes the word “Test” into cells A1 through A5.
No need to worry about row numbers — the loop automatically covers every cell.

---

・Highlight cells with color

Sub HighlightCells()
Dim cell As Range
For Each cell In Range("B1:B10")
cell.Interior.Color = vbYellow
Next cell
End Sub

✅ Each cell in B1:B10 is filled with yellow.
A quick and visual example of how For Each simplifies formatting tasks.

---

✅ Applying Conditions Inside the Loop

You can combine For Each with If statements to apply logic selectively.

・Example: Highlight only cells with numbers greater than 100

Sub HighlightLargeValues()
Dim cell As Range
For Each cell In Range("C1:C20")
If IsNumeric(cell.Value) Then
If cell.Value > 100 Then
cell.Interior.Color = vbRed
End If
End If
Next cell
End Sub

✅ Each numeric value greater than 100 turns red.
✅ Non-numeric or empty cells are skipped safely.

---

・Example: Change font color for text matches

Sub ColorSpecificText()
Dim cell As Range
For Each cell In Range("A1:A20")
If cell.Value = "Completed" Then
cell.Font.Color = vbGreen
End If
Next cell
End Sub

✅ Only cells containing “Completed” are colored green.
✅ Excellent for marking finished tasks or approvals.

---

✅ Skipping Blank Cells in a Range

To avoid unnecessary actions, you can skip blank cells easily:

Sub SkipBlanks()
Dim cell As Range
For Each cell In Range("D1:D15")
If Not IsEmpty(cell.Value) Then
cell.Font.Bold = True
End If
Next cell
End Sub

✅ Only non-empty cells are processed.
✅ Efficient and prevents runtime errors on empty values.

---

✅ Combining For Each with Multiple Columns

You can expand your range to include multiple columns.

Sub ProcessMultiColumnRange()
Dim cell As Range
For Each cell In Range("A1:C5")
cell.Value = cell.Value & "!"
Next cell
End Sub

✅ Adds an exclamation mark to all cells in a 3×5 block (A1:C5).
✅ Perfect for formatting datasets or marking processed records.

---

✅ Using For Each to Copy or Transform Data

・Example: Copy only numeric values

Sub CopyNumericValues()
Dim cell As Range
For Each cell In Range("A1:A20")
If IsNumeric(cell.Value) Then
cell.Offset(0, 1).Value = cell.Value
End If
Next cell
End Sub

✅ Copies numbers from column A to column B.
✅ Automatically skips text or blanks.

---

・Example: Convert text to uppercase

Sub ConvertToUppercase()
Dim cell As Range
For Each cell In Range("B1:B20")
If Not IsEmpty(cell.Value) Then
cell.Value = UCase(cell.Value)
End If
Next cell
End Sub

✅ Changes all text in B1:B20 to uppercase.
✅ A simple yet effective text-cleaning technique.

---

✅ Controlling Complex Conditions Inside a For Each Loop

When processing cell ranges, you might need multi-level conditions.

Sub ConditionalFormattingExample()
Dim cell As Range
For Each cell In Range("C1:C20")
If IsNumeric(cell.Value) Then
Select Case cell.Value
Case Is < 50
cell.Interior.Color = vbYellow
Case 50 To 100
cell.Interior.Color = vbGreen
Case Is > 100
cell.Interior.Color = vbRed
End Select
End If
Next cell
End Sub

✅ Assigns color based on value ranges:

• Yellow: below 50
• Green: between 50 and 100
• Red: above 100

A great way to visualize performance or KPI data dynamically.

---

✅ How to Reference Entire Rows or Columns

For Each can also loop through entire rows or columns — not just individual cells.

・Example: Loop through rows

Sub LoopThroughRows()
Dim r As Range
For Each r In Range("A1:A10").EntireRow
r.Cells(1, 1).Value = "Row " & r.Row
Next r
End Sub

✅ Writes “Row 1”, “Row 2”… etc. in column A.
✅ Useful for row-based data tagging.

---

・Example: Loop through columns

Sub LoopThroughColumns()
Dim c As Range
For Each c In Range("A1:E1").EntireColumn
c.Cells(1, 1).Value = "Column " & c.Column
Next c
End Sub

✅ Labels each column with its column number.
✅ Great for building template-style workbooks.

---

✅ Nested For Each Loops (Two-Dimensional Iteration)

You can nest For Each loops to process two-dimensional ranges, like rows and columns together.

Sub DoubleLoopExample()
Dim rowCell As Range
Dim colCell As Range
For Each rowCell In Range("A1:A5")
For Each colCell In Range("B1:D1")
Cells(rowCell.Row, colCell.Column).Value = "X"
Next colCell
Next rowCell
End Sub

✅ Fills a 5×3 block with “X” marks.
✅ Efficient for structured table manipulation.

---

✅ Combining For Each with Offset for Relative Operations

Offset allows you to move relative to the current cell in the loop.

・Example: Add values to the next column

Sub AddOffsetValues()
Dim cell As Range
For Each cell In Range("A1:A10")
cell.Offset(0, 1).Value = cell.Value * 2
Next cell
End Sub

✅ Doubles the values in column A and writes results in column B.
✅ A clean, no-index approach to cell manipulation.

---

✅ Working with Entire UsedRange Automatically

You can easily process the entire active sheet with UsedRange.

Sub LoopUsedRange()
Dim cell As Range
For Each cell In ActiveSheet.UsedRange
cell.Value = Trim(cell.Value)
Next cell
End Sub

✅ Cleans up leading/trailing spaces in all used cells.
✅ Useful for large, imported datasets.

---

✅ Advanced Example: Conditional Copy Between Sheets

Sub CopyHighScores()
Dim cell As Range
For Each cell In Sheets("Data").Range("A2:A50")
If IsNumeric(cell.Value) And cell.Value >= 80 Then
Sheets("Result").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = cell.Value
End If
Next cell
End Sub

✅ Reads data from “Data” sheet
✅ Copies scores ≥ 80 to the next available row in “Result”
✅ Demonstrates cross-sheet automation with precision

---

✅ Using For Each with Error Handling

Sometimes cells contain invalid or unexpected data.
You can handle them gracefully within a For Each loop.

Sub SafeProcessing()
Dim cell As Range
On Error Resume Next
For Each cell In Range("A1:A10")
cell.Value = cell.Value * 2
Next cell
On Error GoTo 0
End Sub

✅ Prevents errors from stopping your loop (e.g., text in numeric fields).
✅ Always turn error handling back on (On Error GoTo 0) after use.

---

✅ Performance Optimization Tips

For large loops, always optimize performance to prevent lag or freezing.

・Disable unnecessary updates

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'--- Your For Each code here ---
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

✅ Disables screen refresh during processing.
✅ Makes large loops 10x faster.

---

・Use With to simplify object references

With Range("A1:A100")
Dim cell As Range
For Each cell In .Cells
cell.Value = cell.Value + 1
Next cell
End With

✅ Cleaner code
✅ Faster execution due to reduced object calls

---

✅ Common Mistakes and How to Avoid Them

Mistake	Cause	Solution
Forgetting .Cells	Loop doesn’t target specific cells	Always loop through .Cells when using With blocks
Infinite loops	Misusing Do instead of For Each	Use For Each for collections
Skipping errors incorrectly	Overusing On Error Resume Next	Handle only specific cases
Slow performance	Updating screen every iteration	Use ScreenUpdating = False
Unclear variables	Missing Dim statements	Always declare all variables clearly

---

✅ Practical Business Applications

Scenario	How For Each Helps
Cleaning imported data	Trim text and remove spaces
Formatting reports	Color or bold cells by condition
Data validation	Flag empty or invalid entries
Dynamic summaries	Calculate totals across variable ranges
Automated dashboards	Apply highlights or indicators in real time

The For Each loop transforms repetitive Excel tasks into efficient, maintainable automation.

---

✅ Summary：Master “For Each” for Clean and Efficient VBA Automation

• Use For Each to loop through cells, rows, or columns effortlessly.
• Combine with If conditions for selective actions.
• Employ Offset for relative movement between cells.
• Use UsedRange for full-sheet processing.
• Always optimize performance with ScreenUpdating and Calculation settings.
• Avoid overcomplicating code — keep it clean, safe, and readable.

By mastering For Each for cell range operations, you’ll elevate your VBA automation to professional quality — making your Excel tasks faster, smarter, and more reliable.