Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Use VBA for Each Cell in Range in Excel (3 Methods)

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.

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

Basic outline for applying vba code for each cell in range

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

Inserting a command button from control panel

  • We have got our command button.

Having a command button from control panel

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

Click on properties option to label your command prompt..

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

Choose the command prompt caption

  • 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

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

Writing vba code to specify the worksheet.

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

Run the vba code

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

Apply a VBA Code for Each Cell in a Range

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

Output of a VBA Code for Each Cell in a Range

  • 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

  • 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

Apply a VBA Code for Each Cell in a Range

  • Finally, get the result by clicking on the command button.

Apply a VBA Code for Each Cell in a Range


Similar Readings:


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.

Dataset for inserting vba code for column cells

📌 Steps:

  • Firstly, create a command button following the instructions that we have discussed.

Click on the command prompt

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

Output of vba code on column cells


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.

Dataset for writing a VBA Code For Each Cell in a Row

📌 Steps:

  • To begin with, add a command button and change its name to “Click Here!

Add a click here command button

  • 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

VBA code for each cell on a specific row

  • Lastly, click on the button, and our result is here.

Output of vba code for each cell in a row


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.

Dataset for VBA Loop Through Range until Single 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

VBA code for Range until Single Empty Cell

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!


Similar Articles to Explore 

Tags:

Asikul Himel
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo