Active Cell refers to the cell that is currently active at the moment. The ActiveCell Object in VBA is used to refer to the Active Cell of the Current Worksheet of the Active Workbook. The ActiveCell has its methods and properties.
So let us start learning.
How to Activate a Cell using VBA?
We can use the Activate method to activate a Cell. We can refer to a cell with the help of Range or Cells Function.
Range("<column>"&"<row>").Activate Or Cells(<row_number>,<column_number).Activate
Let us say we wish to select the Range A1:D10 of the Active Worksheet. Thereafter, we activate cell B5 of the Selected Range. You can use the following Sub Procedure to select the range A1:A10 and then activate the cell from the selected range of cells.
Sub activateCellB5() 'select the Range A1:D10 of Active Worksheet Range("A1:D10").Select 'activate the cell B5 Cells(5, 2).Activate 'change the interior color of active cell ActiveCell.Interior.Color = RGB(230, 210, 240) End Sub
Highlight Range Around Active Cell
We can use the CurrentRegion property of the Active Cell to refer to the range of cells from the top left cell to the bottom right cell around the active cell. Thereafter, we will change the background color of the current region around the active cell.
Let us say we have a worksheet named Sheet1 and it has the following data.
You can use the following Sub Procedure. The function is used to generate random lighter shades to highlight.
'a function that returns a random integer between 201 and 255 Function random() As Integer Do Until random > 200 random = rnd() * 255 Loop End Function 'sub procedure to highlight area around active cell Sub highlightCurrentRegionAroundActiveCell() 'highlight current region around active cell ActiveCell.CurrentRegion.Select 'activate a cell Range("B3").Activate 'highlight region around active cell ActiveCell.CurrentRegion.Interior.Color = RGB(random, random, random) End Sub
Activate the cell to cell A1. When you run this Sub Procedure the following things happen.
- The Range around the Active Cell is selected.
- Cell B3 is activated.
- The random color generated by the RGB function is set as the background color of the Range around the active cell.
Value in Active Cell
We can refer to the Value of the Active Cell with the help of the Value property of the ActiveCell Object. We are using the MsgBox function to print the value in Active Cell.
You can use the following Sub Procedure.
Sub printValueInActiveCell() 'print the value in active cell using VBA MsgBox ActiveCell.Value End Sub
We can clear the contents of Active Cell by using the Clear method.
Set the Active Cell to an Object
Active Cell refers to the cell which is currently active. We can set a Range Object to refer to the Active Cell.
We can activate a cell and then run this Sub Procedure to change its value using the Range Object.
Sub setActiveCellToRange() 'set the value of active cell to a range variable Dim myCell As Range 'set the active cell to myCell Set myCell = ActiveCell 'change the value of myCell value myCell.Value = "This is new value of Active Cell" End Sub
Get the Row Number and Column Number of the Active Cell
We can use the Row and Column property of the ActiveCell object to get the value of the row number and column number respectively.
Sub getRowAndColumnNumber() 'print the value of row number and column number of active cell MsgBox "Row Number: " & ActiveCell.Row & ", Column Number: " & ActiveCell.Column End Sub
When you run this Sub Procedure, it will print the value of the row number and column number of the active cell.
Get the Address of Active Cell
We can use the Address property of ActiveCell to print its Address.
Move the Active Cell using OFFSET
We can use the OFFSET method of Active Cell to move its location row wise and column wise. It has two arguments.
- row_offset – This is the number of rows that we want to shift from the current address of the active cell. If it is positive then the active cell moves down and if it is negative, the active cell moves up.
- column_offset – This is the number of columns that we want to move from the current location of the active cell. If is positive then the active cell moves to the right and if it is negative then the active cell moves to the left.
For instance, you can use the following Sub procedure to move the active cell from its current location.
Sub moveActiveCell() 'using OFFSET TO MOVE ACTIVE CELL MsgBox ("activate a cell 2 rows below the active cell") ActiveCell.Offset(2, 0).Activate ActiveCell.Interior.Color = RGB(random, random, random) MsgBox ("activate a cell 2 columns to right") ActiveCell.Offset(0, 2).Activate ActiveCell.Interior.Color = RGB(random, random, random) MsgBox ("activate a cell 3 rows above the active cell") ActiveCell.Offset(-3, 0).Activate ActiveCell.Interior.Color = RGB(random, random, random) MsgBox ("activate a cell 4 columns to left") ActiveCell.Offset(0, -4).Activate ActiveCell.Interior.Color = RGB(random, random, random) MsgBox ("activate a cell 3 rows below and 2 columns to right") ActiveCell.Offset(3, 2).Activate ActiveCell.Interior.Color = RGB(random, random, random) End Sub
We are taking the active cell as C3.
This was all about using Active Cell in VBA.
Thank you for reading. ❤