Cells in VBA – Usage With Examples

In one of the previous articles, we learned to select the ranges and cells with the help of VBA in Excel. Today we will learn to work with cells using VBA. We will learn to use the Range and Cells method along with looping through cells with the help of Loops in VBA.

So let us start learning.

VBA Cells and Ranges

The concept of Cells in VBA is the same as Excel Cells. There are two different methods to refer to specific cells in the worksheet. We can use either the Range or Cells property to refer to a single cell in VBA. Both the Range and Cells Property can return a reference to a Range Object that is referencing Worksheet Cell.

You can download the following practice workbook.

Syntax – Range Property

The Range property requires the name of the cell to which you wish to refer.

Range("<Cell>")

For instance, to refer to select the A1 cell in Active Worksheet, you can use any of the two properties.

'using Range property to select a single cell
Range("A1").Select

Syntax – Cells Property

When we are using the Cells Property, it requires the row index and column index. The Cells property returns a reference to the worksheet cell formed by the intersection of row and column.

The syntax to use Cells Property in Excel is as follows.

Cells(<row_index>,<column_index>)

We can select cell A1 using the Cells property.

Cells(1,1).Select

Change the Value in the Cell using VBA

We can use the Value property of Cells to change the value of the cell referred by the Cells property.

For instance, you can change the value in Cell B4 in the Active Sheet with the help of the following Sub Procedure.

Sub changeValueOfCellB4()
'changing the value of cell using VBA
Cells(4, 2).Value = "Learning MS Excel"
'changing the interior color of cell
Cells(4, 2).Interior.Color = RGB(204, 255, 229)
End Sub

When you run this Sub Procedure, it changes the value in cell B4 of the active sheet to “Learning MS Excel” and changes the back color of the cell.

Change Cell Value in Another Worksheet using VBA

This is not necessary that we wish to change the cell value in the active sheet only. We can refer to the cells of a specified worksheet (not active at the moment ) and then change the value of the cell.

Let us say we have this workbook containing two worksheets named as MyData and HyData. We have currently opened the MyData Worksheet and wish to change the value of cell A2 in Worksheet HyData.

Sub ChangeCellValueAnotherWoeksheet()
'change the value of cell in another sheet using VBA
Worksheets("HyData").Cells(2, 1).Value = "Updated the Value"
'change the color of cell
Worksheets("HyData").Cells(2, 1).Interior.Color = RGB(229, 204, 255)
'now activate the worksheet to see result
Worksheets("HyData").Activate
End Sub

Change Cell Value in Another Workbook

We can use the Workbooks collection to refer to the different opened workbooks. Thereafer we specify the Worksheet of opened workbook using Worksheets Collection. For instance, you can use the following Macro to change the value of a cell in MyWorkbook.xlsx Workbook.

Sub ChangeValueOfCellAnotherWorkbook()
Workbooks("MyWorkbook.xlsx").WorksheetS(1).Cells(1,1)="Value"
End Sub

Looping Through Cells using Cell Property

We can use the For Next loop in VBA to loop through different cells using the Cell Property. We can replace the column index or row index in the Cells property with an expression containing the counter variable of For Next Loop.

For instance, let us say I wish to change the interior color of all the odd numbered rows to blue.

Sub highlightOddRows()
'highlight odd rows
Dim i As Integer
For i = 1 To 1000 Step 2
'change the interior color of the entire row corresponding to cell
Cells(i, 1).EntireRow.Interior.Color = RGB(143, 188, 219)
Next i
End Sub

When you run this Sub Procedure, it refers to cell A1,A3,A5,A7…..A2000 and then changes the interior color of the entire row corresponding to these cells.

Using Cells Property in Range

We can refer to a cell of a specific Range by using the Cells and Range Property together. Let us say I have data in Range B2:D5.

We can refer to specific cells within this range only. The column and row indexing will start from this range and not from cell A1.

Sub changeCellWithinRange()
'highlight the cell formed by intersection of 2nd row, 2nd column in the range B2:D5
Range("B2:D5").Cells(2, 2).Interior.Color = RGB(229, 204, 255)
End Sub

When you run this Sub procedure, it changes the color of cell in the range B2:D5 (2nd Column and 2nd Row).

This brings us to an end.

Thank you for reading.❤