VBA for Each Cell in Range in Excel (3 Methods)

In big Databases, you spent most of your time dealing with ranges and cells. Sometimes you have to repeat the same action in large ranges or a large number of cells. This at the same time kills your time and reduces your efficiency. The smart solution to this problem is to build a VBA Programming Code which will run through each cell in the range and perform the same action directed by you. Today in this article, we will discuss how to do VBA for Each Cell in Range in Excel.

Table of Contents hide

Download Practice Workbook

3 Suitable Ways to Apply VBA for Each Cell in Range in Excel

1. Apply VBA for Each Cell in Range

2. Insert VBA Code For Each Cell in a Column of a Range

3. Write a VBA Code For Each Cell in a Row of a Range

Quick Notes

Conclusion

Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.

VBA Code for Each Cell in a Range in Excel.xlsx

3 Suitable Ways to Apply VBA for Each Cell in Range in Excel

Using a VBA code you can perform the same formula to each cell in a range or a column or a row. In this section, we will go through all the sections.

1. Apply VBA for Each Cell in Range

Consider a situation where you have to apply the same VBA code for each cell in a given range (B3:F12). To do this we will build a VBA code. The instructions are given below

Step 1:

First, we will insert a command button to simplify our job. Go to your Developer Tab, select Insert, and click on the command button to get one.

We have got our command button.

Right-click on the command button to open options. Select and click on Properties to modify some options.

Change the Caption of the command button. Here we change the name to, “Click Here”.

Step 2:

Now double-click on the command button to open the VBA Module. Here we will write down our VBA code for the task.

First, we will declare two variables. We call the range objects CL and Rng. You can name them as you like.

Dim CL As Range
Dim Rng As Range

Assign specific range by this command,

Set Rng = Worksheets(“VBA1”).Range(“B3:F12”)

Here VBA1 is our worksheet name and B3:F12 is our defined range.

Now we will use the code to perform through each cell in the range. The code is,

For Each CL In Rng
CL.Value = 100
Next CL

Value = 100 refers that it will return 100 for each cell in the given range.

So our final code becomes,

Private Sub CommandButton1_Click ()
Dim CL As Range
Dim Rng As Range
Set Rng = Worksheets("VBA1").Range("B3:F12")
For Each CL In Rng
CL.Value = 100
Next CL
End Sub

Go to your main worksheet and click on the command button to run VBA for each cell in the range.

Step 3:

Like numeric values, we can also put text values for each cell in the range. In that case, go to the VBA window, and instead of 100, insert the text value you want to run through. The changed line is

CL.Value = “ExcelDemy”

Click on the command button and the VBA code will return this text value for each cell in the range.

Step 4:

In this step, we will dig a little deeper. Let’s say we want to highlight each blank cell in our range.

For that, add a new condition to your existing code. The new formula is,

If CL.Value = “ ” Then
CL.Interior.ColorIndex = 3
End If

This new code will highlight the blank cell with red color. So the full code is,

Private Sub CommandButton1_Click ()
Dim CL As Range
Dim Rng As Range
Set Rng = Worksheets("VBA1").Range("B3:F12")
For Each CL In Rng
If CL.Value = “ ” Then
CL.Interior.ColorIndex = 3
End If
Next CL
End Sub

Get the result by clicking on the command button.

Similar Readings:

2. Insert VBA Code For Each Cell in a Column of a Range

We can run VBA code for each cell in a column too. Suppose we have a column containing numbers and we have to color values that are lower than 10. We will build a VBA code to run each cell in the column.

Step 1:

Create a command button following the instructions that we have discussed.

Step 2:

Double-click on the command button to open the VBA window.
We will declare a variable called “c” of type long. We are using the type Long variable here because Long variables have a larger capacity than Integer variables.

Dim c As Long

Next, add the code line that changes the font color of all the cells in our column to black.

Columns(1).Font.Color = vbBlack

Insert the loop for this code.

For c = 1 To Rows.Count
Next c

In this step, we will enter a condition to color the values that are lower than the value of cell C4 (10). Enter this code to do that.

If Cells(c, 1).Value < Range("C4").Value And Not IsEmpty(Cells(c, 1).Value) Then
Cells(c, 1).Font.Color = vbRed
End If

So the final code is,

Private Sub CommandButton1_Click ()
Dim c As Long
Columns(1).Font.Color = vbBlack
For c = 1 To Rows.Count
If Cells(c, 1).Value < Range("C4").Value And Not IsEmpty(Cells(c, 1).Value) Then
Cells(c, 1).Font.Color = vbRed
End If
Next c
End Sub

The VBA will run and show results when you click on the command button.

3. Write a VBA Code For Each Cell in a Row of a Range

We can run a VBA code for each cell in a row too. In the given row, we need to perform the same action on each cell of the row.

Step 1:

Add a command button and change its name to “Click Here!”

Double-click on the button to open the VBA window. Write down the VBA code provided below.

Private Sub CommandButton1_Click ()
Dim r As Range
Dim MyString As String
'For Each cell in a row, and apply a yellow color fill
For Each r In Range("B3:F3").Rows
r.Interior.ColorIndex = 6
Next
End Sub

The code will run through each cell of the row and apply a yellow color fill to each cell.

Click on the button and our result is here.

Quick Notes

👉 If you do not have your developer tab visible, you can activate it using this instruction.

Customized Quick Access Toolbar → More Commands → Customize Ribbon → Developer → OK

Conclusion

We have gone through three different approaches to run VBA for each cell in a range. You are most welcome to comment if you have any questions or queries. You can also check out our other articles related to the Excel tasks!