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
- 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”.
- 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.
- 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.
- 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.
- How to Use the Range Object of VBA in Excel (5 Properties)
- Use VBA Range Offset (11 Ways)
- How to Count Text in Excel (7 Easy Tricks)
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.
- Create a command button following the instructions that we have discussed.
- 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.
- 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.
👉 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
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!