Excel VBA code to loop through cells

loop-through-cells-vba

why loop through cells?

In Excel VBA looping through cells play a significant role in almost any project that we do, looping through cells adds more value to our code and makes life easier. Suppose we have huge data and if we want to find values in each cell is an odd number or an even number checking manually takes more time to do. That is where looping through cells come in to picture in VBA. We will see the same as an example in our code.

Looping through cells can be achieved in many methods like using for next loop and for each loop.

What does this code do?

Looping through cells in VBA can be done in many ways but in this, I will be using for each next statement. Additionally, as an example we will check whether the value in each cell is an odd number or even.

This code loops through all the used cells i.e cells with values and, find if that number is an odd number or an even number. If it is an odd number it styles the number in red color and if it is an even change the color to blue.

Step 1:

Declaring variables is a good practice in VBA as it improves in performance of the code and also the use of option explicit at the beginning. So we declare variables required for this program.

Dim rng As Range'variable declaration as range
Dim cell As Range'variable declaration as range
Step 2:

Finding the used cell range using UsedRange, property is important because looping through empty cells is a waste of time and more resource consuming process that is why we find the used cell range and loop only through used range which reduces time and resources on execution.Here used range address is stored in "rng" variable.

Set rng = ActiveSheet.UsedRange 'setting used range address to rng
Step 3:

As mentioned above looping cells can be done many ways, here I used for each statement to loop through cells.

The way it works is for each statement loops through each cell in variable rng and checks whether the value is odd or even. For this, we use the Mod operator.

For example, if cell. Value Mod 2 = 0 is equal to 0 then the value in the cell is an even number as its remainder is 0 on dividing the cell value by 2.

If cell. Value Mod 2 = 0 not equal to 0 then the value in the cell is an odd number as its remainder is 1 on dividing the cell value by 2 and therefore clearly it is not divisible by 2.

For Each cell In rng
If (cell.Value Mod 2 = 0) Then
cell.Font.Color = vbBlue
Else
cell.Font.Color = vbRed
End If
Next cell
End Sub
Final code:
Option Explicit
Sub finddata()
Dim rng As Range'variable declaration as range
Dim cell As Range'variable declaration as range
Set rng = ActiveSheet.UsedRange 'setting used range address to rng
For Each cell In rng
If (cell.Value Mod 2 = 0) Then
cell.Font.Color = vbBlue
Else
cell.Font.Color = vbRed
End If
Next cell
End Sub
Result
loop-through-cells-vba

As you can see we looped through all the cells and checked for odd or even number.

The project source code file is available for download you can download it from the below link. Try experimenting with this code to get clarity.

Hope you liked this article, if so please share it with your friends.

Try experimenting with these codes to get clarity.

Excel Logo

Download project file

If you want more clarity or demo of this code, please watch the below youtube video.

Detailed Video: How to find values using for each next loop

Share on:
Most Popular