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.

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.

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`

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`

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`

`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`

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.

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

Most Popular