How to make a cell an active cell in Excel

If you have a large worksheet, maybe it is hard for you to find out the active cell or active selection at a glance. But, if the active cell/section has an outstanding color, to find out it will not be a problem. In this article, I will talk about how to automatically highlight the active cell or selected range of cells in Excel.

Highlight active cell or selection with VBA code

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...

Read More... Free Download...

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

Read More... Free Download...

Highlight active cell or selection with VBA code

The following VBA code can help you to highlight the active cell or a selection dynamically, please do as follows:

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Then choose ThisWorkbook from the left Project Explorer, double click it to open the Module, and then copy and paste following VBA code into the blank Module:

VBA code: Highlight active cell or selection

Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range) 'Update 20140923 Static xLastRng As Range On Error Resume Next Target.Interior.ColorIndex = 6 xLastRng.Interior.ColorIndex = xlColorIndexNone Set xLastRng = Target End Sub

3. Then save and close this code, and go back to the worksheet, now, when you select a cell or a selection, the selected cells will be highlighted, and it will be dynamically moved as the selected cells changes.

Notes:

1. If you cannot find the Project Explorer Pane in the window, you can click View > Project Explorer in the Microsoft Visual Basic for Applications window to open it.

2. In the above code, you can change .ColorIndex = 6 color to other color you like.

3. This VBA code can be applied to all worksheets within the workbook.

4. If there are some colored cells in your worksheet, the color will be lost when you click the cell and then move to other cell.

Related article:

How to auto-highlight row and column of active cell in Excel?

No ratings yet. Be the first to rate!

An Excel workbook can contain any number of individual worksheets. As you move around within the various worksheets, Excel keeps track of which cell is selected in which worksheet. When you switch to a new worksheet, Excel makes active the cell that was last active within that worksheet. Thus, if you last selected cell F9 in the worksheet, that is the one that is selected when you display the worksheet again, regardless of what was selected in the previous worksheet.

For some workbooks, however, you may want Excel to make the active cell in the selected worksheet the same as the active cell in the previous worksheet. There is no setting to automatically do this in Excel, but there are a couple of things you can try. One thing is to follow these steps:

Hold down the Ctrl key as you click on the tab of the worksheet you want to go to. Two worksheet tabs should now be selected; the one with the bold name is the one that is actually displayed on the screen.
Click on the tab for the worksheet you want to go to. Both tabs should still be selected, but just the one you clicked on should have its name in bold.
Hold down the Ctrl key as you click on the tab of the worksheet you just left.

These steps work because you are "grouping" worksheets. When you do, Excel makes the selected cells the same for all worksheets in the group.

Remembering to use the Ctrl-click-click-Ctrl sequence can be cumbersome, at best. It is also a sequence that can be fraught with danger, because if you forget to do step 3, you could end up making unintended changes on your worksheets. (While you are working with grouped worksheets, any change you make on one sheet is similarly changed on all the sheets in the group.)

These three steps cannot be automated with macros, but you can take a different approach with a macro to accomplish the same task. The first thing you need to do is declare a public variable anywhere within a module of the workbook, as shown here:

Public sAddress As String

This variable, sAddress, will be used to store the current address of the active cell. In the "ThisWorkbook" module of the workbook, add these two macros:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Excel.Range) sAddress = ActiveCell.Address End Sub Private Sub Workbook_SheetActivate(ByVal Sh As Object) On Error Resume Next If sAddress > "" Then Sh.Range(sAddress).Select End Sub

The first macro is run automatically by Excel any time that the selected cell changes. All it does is retrieve the address of whatever cell is active, and then store that address in the sAddress variable.

The second macro is automatically run whenever a workbook is activated. It checks to see if there is anything stored in sAddress. If there is, it selects whatever cell address is stored there. The error code is necessary in case you select a sheet that doesn't use cells, such as a chart sheet.

This macro approach works great if you only want to make this navigational change in a single workbook or two. If you prefer to make the change "system wide" (so to speak), you must be a little more complex in your approach to the macro. In this case, you need to place your code in the Personal.xls workbook so that it is loaded every time you start Excel. Specifically, place the following code into a new class module of the Personal.xls workbook. This class module should be named something descriptive, such as ClassXLApp:

Public WithEvents gobjXLApp As Excel.Application Private mstrAddress As String Private Sub gobjXLApp_WorkbookActivate(ByVal Wb As Excel.Workbook) On Error Resume Next If mstrAddress > "" Then ActiveSheet.Range(mstrAddress).Select End Sub Private Sub gobjXLApp_SheetActivate(ByVal Sh As Object) On Error Resume Next If mstrAddress > "" Then Sh.Range(mstrAddress).Select End Sub Private Sub gobjXLApp_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Excel.Range) mstrAddress = Selection.Address End Sub

Next, open the "ThisWorkbook" module of Personal.xls and copy the following code to it:

Private mobjXLApp As New ClassXLApp Private Sub Workbook_Open() Set mobjXLApp.gobjXLApp = Excel.Application End Sub

Once you save Personal.xls and restart Excel, the range in the first workbook that opens will be selected in the next worksheet that is selected.

Note:

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3205) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Maintaining the Active Cell.

In spreadsheet programs such as Excel or Google Spreadsheets, the active cell is identified by a colored border or outline surrounding the cell. The active cell is always in the active sheet.

The active cell is also known as the current cell or the cell that holds the focus of the cursor. Even if you've selected several cells, only one normally has focus, which, by default, is selected to receive input. For example, data entered with the keyboard or pasted from a clipboard is sent to the cell which has focus. An exception is when an array formula is entered into multiple cells at the same time.

Similarly, the active sheet or current sheet is the worksheet containing the active cell. Like the active cell, the active sheet is considered to have focus when it comes to performing actions that affect one or more cells — such as formatting — and the changes occur to the active sheet by default.

The active cell and sheet can easily be changed. In the case of the active cell, either clicking another cell with the mouse pointer or pressing the arrow keys on the keyboard will both result in a new active cell being selected.

Change the active sheet by clicking a different sheet tab with the mouse pointer or by using a keyboard shortcut.

The cell reference for the active cell appears in the Name Box, located above Column A in a worksheet. If the active cell has been given a name, either on its own or as part of a range of cells, the range name is displayed in the Name Box instead.

If a group or range of cells have been selected the active cell can be changed without re-selecting the range using the following keys on the keyboard:

Enter: moves the active cell highlight down one cell within the selected range.
Shift+Enter: moves the active cell highlight up one cell within the selected range.
Tab: moves the active cell one cell to the right within the selected range.
Shift+Enter: moves the active cell one cell to the left within the selected range.
Ctrl + . (period): moves the active cell clockwise to the next corner of the selected range.

If more than one group or range of non-adjacent cells is highlighted in the same worksheet, the active cell highlight can be moved between these groups of selected cells using the following keys on the keyboard:

Ctrl+Alt+Right Arrow: moves the active cell highlight to the next non-adjacent range to the right of the current location.
Ctrl+Alt+Left Arrow: moves the active cell highlight to the next non-adjacent range to the left of the current location.

Even though it is possible to select or highlight more than one worksheet at one time, only the active sheet name is in bold and most changes made when multiple sheets are selected will still only affect the active sheet.

Change the active sheet by clicking the tab of another sheet with the mouse pointer, or use shortcut keys: