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.
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.
Also Read: Selection Property VBA – Usage With Examples
The syntax to use Cells Property in Excel is as follows.
We can select cell A1 using the Cells property.
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.
Also Read: Select Cells and Ranges using VBA in Excel
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.❤