In this example we will see how to use the ActiveCell property of VBA. The ActiveCell property returns a Range object of the active cell in the active workbook. You can then apply any of the properties or methods of a Range object to it.

Though you may find a very practical scenarios to use ActiveCell, it is best to avoid using it as much as possible. The reason being, if you accidentally change the active cell during the execution of the macro or you happen to open / activate another workbook, it will cause run-time errors in your code.

So, in this article, I will also suggest few ways to avoid the use of ActiveCell and also how to minimize some errors that can result from using ActiveCell.

Before looking at a practical example, let us have a look at a few examples on how to use the ActiveCell property and the various methods / properties associated with it.

Example 1: Getting the value of the active cell

This example shows how to display the value of an active cell in a message box

    Dim selectedCell As Range
    Set selectedCell = Application.activecell
    MsgBox selectedCell.Value

However, please note that if another workbook is active, the ActiveCell from that workbook will be considered, irrespective of workbook in which the code is written. So, always make sure to activate the current workbook before using ActiveCell.
So, a better way of doing it will be
    Dim selectedCell As Range
    ThisWorkbook.Activate
    Set selectedCell = Application.activecell
    MsgBox selectedCell.Value

Example 2: Getting the address / row / column of the active cell
Use the following properties to display the address / row number and column number of the active cell
    Dim selectedCell As Range
    ThisWorkbook.Activate
    Set selectedCell = Application.activecell
    MsgBox selectedCell.Address
    MsgBox selectedCell.Row
    MsgBox selectedCell.Column

The address will be displayed as below:

And for row and column, the respective numbers will be displayed.


Example 3: Activating / Selecting a cell
You can make a cell active either by using the activate method or by using the select method on the range you want to activate
    ThisWorkbook.Activate
    Worksheets("Sheet1").Range("B4").Activate

which is equivalent to
    ThisWorkbook.Activate
    Worksheets("Sheet1").Range("B4").Select

And now when you run MsgBox selectedCell.Address you will get the output as “$B$4”
Example 4: Select last row in the active column using ActiveCell
Consider the below snapshot. The ActiveCell is in the first column.

If you want to select the last cell that has data in this active column, you can use the code snippet below.
    ThisWorkbook.Activate
    Application.activecell.End(XlDirection.xlDown).Select

After the execution of the code, the last cell will be selected like this

Example 5: Selecting the cells that contain data around the ActiveCell using CurrentRegion
The CurrentRegion property returns a range of cells bounded by blank rows and columns. In the following example, the selection is expanded to include the cells adjoining the active cell that contain data and the background color of the current region is modified. The entire range is copied to another sheet at the same location.
Sub CurrRegion()

    Dim curReg As Range

    ThisWorkbook.Activate

    'Get the current region and assign it to a Range variable
    Set curReg = ActiveCell.CurrentRegion
    ActiveCell.CurrentRegion.Select

    'Format it
    Selection.Interior.Color = vbCyan

    'Select the first cell in the range
    Application.ActiveCell.End(XlDirection.xlUp).Select
    Application.ActiveCell.End(XlDirection.xlToLeft).Select

    'Paste the current region in another sheet at the same location
    curReg.Copy Worksheets("Sheet2").Range(Application.ActiveCell.Address)

End Sub


The current region can be useful when the range of data is not fixed and you need to perform certain operations on it, like format it, copy it, insert charts or send an email using that range.
Example 6: Using offset with ActiveCell
Now let us look at a more practical example of using ActiveCell. Say, in your Excel sheet you have multiple lines of data as shown below and you need to process data only for a single selected row.

The desired output is that in the column “Gaining”, Y or N should be inputted based on the price level and the background color should be set to Green or Red respectively.
Here, we will assume that the stock name is selected before running the macro. To get the values of the Previous Close and Current Price we will use the Offset Method
Sub offset()

    Dim preClose As Double, currPrice As Double

    ThisWorkbook.Activate

    'Get value from the second column i.e. offset of zero rows and one column
    preClose = ActiveCell.offset(0, 1).Value

    'Get value from the third column i.e. offset of zero rows and two columns
    currPrice = ActiveCell.offset(0, 2).Value

    If currPrice < preClose Then
        'Set value and format of the forth column i.e. offset of zero rows and three columns
        ActiveCell.offset(0, 3).Value = "N"
        ActiveCell.offset(0, 3).Interior.Color = vbRed
    Else
        ActiveCell.offset(0, 3).Value = "Y"
        ActiveCell.offset(0, 3).Interior.Color = vbGreen
    End If

End Sub

For further details on the Offset Method, please refer to the article “How to use VBA Range.Offset Method”
You can easily avoid the use of ActiveCell here, by using the input box to get the stock name / row number from the user.  After running the code on the last row the output will look like this.

See also: “VBA, Excel Automation From Other Applications“