Asikul Himel
In big Databases, you spend 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. However, the smart solution to this problem is to build a VBA 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 use VBA for each cell in a range in Excel.
In addition, an overview image has been given below that depicts the application of VBA code for each cell in a 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. Applying 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 (B4:F13). To do this we will build a VBA code. The instructions are given below
📌 Steps:
- 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 a specific range by this command,
Set Rng = Worksheets("Each Cell in Range").Range("B4:F13")Here Each Cell in Range is our worksheet name and B4:F13 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("Each Cell in Range").Range("B4:F13")
For Each CL In Rng
CL.Value = 100
Next CL
End Sub- Go to your main worksheet and click on the Click Here 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. However, the changed line is
CL.Value = “ExcelDemy”
- Next, 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.
- Firstly, add a new condition to your existing code. The new formula is,
If CL.Value = "" Then
CL.Interior.ColorIndex = 7
End If- Secondly, 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("Each Cell in Range").Range("B4:F13")
For Each CL In Rng
If CL.Value = "" Then
CL.Interior.ColorIndex = 7
End If
Next CL
End Sub
- Finally, get the result by clicking on the command button.
Similar Readings:
- 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. Inserting VBA Code For Each Cell in Column
We can run the 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.
📌 Steps:
- Firstly, create a command button following the instructions that we have discussed.
- Secondly, Double-click on the command button to open the VBA window.
- Next, type the following code in your VBA window.
Private Sub CommandButton1_Click()
Dim c As Long
Columns(2).Font.Color = vbBlack
For c = 1 To Rows.Count
If Cells(c, 2).Value < Range("D5").Value _
And Not IsEmpty(Cells(c, 2).Value) Then
Cells(c, 2).Font.Color = vbRed
End If
Next c
End Sub⚡ Code Breakdown:
The code is divided into 2 steps.
- In the first portion, c is taken as a Long variable, and the font color has been set as black by setting Font.Color function.
- In the second portion of the given code, For loop sets to check on each row if it exceeds to D5 value. If not, then the font color will be Red.
- Finally, the VBA will run and show results when you click on the command button.
3. Employing VBA For Each Cell in Row
Finally, 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.
📌 Steps:
- To begin with, add a command button and change its name to “Click Here!”
- Following that, 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⚡ Code Breakdown:
The code is divided into 2 steps.
- In the first portion, r is taken as Range and Mystring as String variable.
- In the second portion of the given code, For loop is set to fill the cells on a specific row with yellow color by means of Interior.ColorIndex=6
- Lastly, click on the button, and our result is here.
How to Use Excel VBA Loop Through Range until Single Empty Cell
If you have to deal with a large set of data, then finding an empty cell seems tiresome for you. Thus here we are going to provide a VBA code to aid your problem.
First of all, look at the dataset given below, where we can see B7 cell is empty. Now we will develop a VBA code to find the empty cell.
- Press Alt + F11 to open your Microsoft Visual Basic for Applications.
- Write the following VBA code in Sheet4.
Sub FirstEmpty()
Dim A As Integer
Application.ScreenUpdating = False
ROWNUM = Range("B5", Range("B5").End(xlDown)).Rows.Count
Range("B5").Select
For A = 1 To ROWNUM
ActiveCell.Offset(1, 0).Select
Next
Application.ScreenUpdating = True
End Sub
See the output as given below.
Things to Remember
👉 Additionally, 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
Summing up, we have gone through three different approaches to run VBA for each cell in a range. However, you are most welcome to comment if you have any questions or queries. You can also check out our other articles related to Excel tasks!
