While you don’t have to care about the active cell when you’re working on the worksheet, it’s an important thing to know when working with VBA in Excel. Proper use of the active cell in Excel VBA can help you write better code. In this tutorial, I first explained what is an active cell, and then show you some examples of how to use an active cell in VBA in Excel.

What is an Active Cell in Excel?

An active cell, as the name suggests, is the currently active cell that will be used when you enter any text or formula in Excel. For example, if I select cell B5, then B5 becomes my active cell in the worksheet. Now if I type anything from my keyboard, it would be entered in this cell, because this is the active cell. While this may sound obvious, here is something not that obvious – when you select a range of cells, even then you would only have one active cell. For example, if I select A1:B10, although I have 20 selected cells, I still have only one single active cell. So now, if I start typing any text or formula, it would only be entered in the active cell. You can identify the active cell by looking at the difference in color between the active cell in all the other cells in the selection. You would notice that the active cell is of a lighter shade than the other selected cells. Another quick way to know which cell is the active cell is by looking at the Name box (the field that is next to the formula bar). The cell reference of the active cell would be shown in the Name Box.

Using Active Cell in VBA in Excel

Now that I’ve explained what is an active cell in a worksheet in excel, let’s learn how an Active cell can be used in Excel VBA.

Active Cell Properties and Methods

In VBA, you can use an active cell in two ways: Here is how you can access all the properties and methods of an active cell in VBA: As soon as you do this, you would notice that a set of properties and methods appear as a drop-down (this is called an IntelliSense in Excel VBA). In the drop-down that appears, you would see two types of options – the one that has a green icon and the one that has a gray icon (with a hand). The one with the grey icons is the Properties, and the one with the green icons is the Methods. Some examples of Methods would include Activate, AddComment, Cut, Delete, Clear, etc. As you can notice, these are actions that can be performed on the active cell. Some examples of Properties would include Address, Font, HasFormula, Interior.Color. All these are properties of the active cell that gives you information about that active cell. For example, you can use this to get the cell address of the active cell or change the interior cell color of the cell. Now let’s have a few simple VBA code examples that you can use in your day-to-day work when working with active cell in excel

Making a Cell the Active Cell

To make any cell the active cell, you first have to make sure that it is selected. If you only have one single cell selected, it by default becomes the active cell. Below is the VBA code to make cell B5 the active cell: In the above VBA code, I have first specified the cell address of the cell that I want to activate (which is B5), and then I use the activate method to make it the active cell. When you only want to make one single cell the active cell, you can also use the select method (code below): As I mentioned earlier, you can only have one active cell even if you have a range of cell selected. With VBA, you can first select a range of cells, and then make any one of those cells the active cell. Below the VBA code that would first select range A1:B10, and then make cell A5 the active cell:

Clear the Active Cell

Below is the VBA code that would first make cell A5 the active cell, and then clear its content (cell content as well any formatting applied to the cell). Note that I have shown you the above code just to show you how the clear method work with active cell. In VBA, you don’t need to always select or activate the cell to perform any method on it. For example, you can also clear the content of cell A5 using the below code:

Get the Value from the Active Cell

Below the VBA code that could show you a message box displaying the value in the active cell: Similarly, you can also use a simple VBA code to show the cell address of the active cell (code below): The above code would show the address in absolute reference (such as $A$5).

Formating the Active Cell (Color, Border)

Below the VBA code that would make the active cell blue in color and change the font color to white. Note that I have used the inbuilt color constant (vbBlue and vbWhite). You can also use the RGB constant. For example, instead of vbRed, you can use RGB(255, 0, 0)

Offsetting From the Active Cell

VBA in Excel allows you to refer to cells relative to the position of the active cell (this is called offsetting). For example, if my active cell is cell A1, I can use the offset property on the active cell and refer to the cell below it by specifying the position of that row corresponding to the active cell. Let me show you an example. The above code first activate cell A1 and makes it the active cell. It then uses the offset property on the active cell, to refer to the cell which is one row below it. And in the same line in the code, I have also assigned a value “Test” to that cell which is one row below the active cell. Let me show you another example where offsetting from the active cell could be used in a practical scenario. Below I have a VBA code that first activates cell A1, and then uses the offset property to cover 10 cells below the active cell and enter numbers from 1 to 10 in cell A1:A10. The above code uses a For Next loop that runs 10 times (and each time the value of the variable ‘i’ increases by 1). And since I am also using ‘i’ in the offset property, it keeps going one cell down with each iteration.

Get ActiveCell Row or Column Number

Below the VBA code that will show you the row number of the active cell in message box: And the below code will show you the column number in a message box:

Assign Active Cell Value to a Variable

You can also use VBA to assign the active cell to a variable. Once this is done, you can use this variable instead of the active cell in your code. And how does this help? Good Question! When you assign the active cell to a variable, you can continue to use this variable instead of the active cell. The benefit here is that unlike the active cell (which can change when other sheets or workbooks are activated) your variable would continue to refer to the original active cell it was assigned to. So if you are writing a VBA code that cycles through each worksheet and activates these worksheets, while your active cell would change as new sheets are activated, the variable to which you assigned the active cell initially wouldn’t change. Below is an example code that defines a variable ‘varcell’ and assigns the active cell to this variable.

Select a Range of Cells Starting from the Active Cell

And the final thing I want to show you about using active cell in Excel VBA is to select an entire range of cells starting from the active cell. A practical use case of this could be when you want to quickly format a set of cells in every sheet in your workbook. Below is the VBA code that would select cells in 10 rows and 10 columns starting from the active cell: When we specify two cell references inside the Range property, VBA refers to the entire range covered between there two references. For example, Range(Range(“A1”), Range(“A10”)).Select would select cell A1:A10. Similarly, I have used it with active cell, Where the first reference is the active cell itself, and the second reference offsets the active cell by 10 rows and 10 columns. I hope this tutorial has been useful for you in understanding how active cell works in Excel VBA. Other Excel tutorials you may also find useful:

Highlight the Active Row and Column in a Data Range in Excel24 Useful Excel Macro Examples for VBA Beginners (Ready-to-use)How to Filter Cells with Bold Font Formatting in Excel (An Easy Guide)How to Delete Entire Row in Excel Using VBACopy and Paste Multiple Cells in Excel (Adjacent & Non-Adjacent)Working with Worksheets using Excel VBA (Explained with Examples)Using Workbook Object in Excel VBA (Open, Close, Save, Set)Excel VBA Events – An Easy (and Complete) GuideHow to Edit Cells in Excel? (Shortcuts)