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.


Download Practice Workbook

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


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

Apply a VBA Code for Each Cell in a Range

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.

Apply a VBA Code for Each Cell in a Range

  • We have got our command button.

Apply a VBA Code for Each Cell in a Range

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

Apply a VBA Code for Each Cell in a Range

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

Apply a VBA Code for Each Cell in a Range

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.

Apply a VBA Code for Each Cell in a Range

  • 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

Apply a VBA Code for Each Cell in a 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.

Apply a VBA Code for Each Cell in a 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.

Apply a VBA Code for Each Cell in a 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.

Apply a VBA Code for Each Cell in a 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”

Apply a VBA Code for Each Cell in a Range

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

Apply a VBA Code for Each Cell in a 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.

Apply a VBA Code for Each Cell in a 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

Apply a VBA Code for Each Cell in a Range

  • Get the result by clicking on the command button.

Apply a VBA Code for Each Cell in a Range


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.

For Column

Step 1:

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

For Column

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

For Column

  • Next, add the code line that changes the font color of all the cells in our column to black.
Columns(1).Font.Color = vbBlack

For Column

  • Insert the loop for this code.
For c = 1 To Rows.Count
Next c

For Column

  • 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

For Column

  • 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.

For Column


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.

Write a VBA Code For Each Cell in a 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.

For row

  • Click on the button and our result is here.

For row


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!


Similar Articles to Explore 

Tags:

Asikul Himel
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo