Excel Highlight Active Cell

Use this macro to highlight the active cell on an Excel worksheet. Temporary lines will appear on the sheet, above and to the left of the active cell.

Introduction

To make it easier to see the active cell, you can add a macro to your Personal Workbook, following the steps below. This will add line shapes above, and to the left of the active cell.

Because the Personal Workbook opens automatically when Excel opens, this macro will run in any workbook that you open while working in Excel.

NOTE: If you don't want this feature to always be on, you can store the code in a different workbook. Then, open that workbook when you want the feature available.

Benefit and Warning

You might have seen other macros that highlight the active cell, and they often change the fill colour of the active cell. If your worksheet has cells with fill colour already, those macros will override that formatting.

With this method, by Phil Treacy from My Online Training Hub, the cell formatting is not changed.

However, there is one important warning -- using macros like this one will prevent you from using the Undo feature in Excel

Set Up a Personal Workbook

If you don’t have a Personal Workbook, follow these steps to set one up:

1. Turn on the Macro Recorder
2. Name the macro, MYTEST, and store it in the Personal Macro Workbook
3. With the Macro Recorder on, type something in a blank cell
4. Turn off the Macro Recorder
5. (optional) Click the Undo button, to remove the text that you typed

View the Personal Workbook Code

Next, follow these steps, to find the module where you’ll add the macro code:

1. Press Alt+F11 to open the Visual Basic Editor
2. In the list of Projects at the left, find the Personal Workbook VBAProject(PERSONAL.XLSB)
3. If necessary, click the + at the left of that project, to see its contents
4. Then, click the + to see the contents of its Microsoft Excel Objects
5. Right-click on the "ThisWorkbook" object, and click View Code

Find and Copy the Code

Next, follow these steps to find and copy the code that you'll paste into that code module:

1. Go the My Online Training Hub, to view this comment by Phil Treacy, on the article about highlighting the selected cell.
2. Click the "and here’s the lines (this is a text file)" link in that comment



3. Open the text file that you downloaded from that link.

Copy to Personal Workbook

Follow these steps to get the macro code into your Personal Workbook:

1. Copy all the text in that file
2. Go back to your Personal Workbook code, where you can see the "ThisWorkbook" code module
3. Paste in the text that you copied
4. Scroll to the top of the module, and make sure there is only one "Option Explicit" line. If there are two of them, delete one.
5. Click the Save button in the tool bar at the top of the code window

Using the Macro

To start using the macro, follow these steps:

1. Close Excel, and reopen it.
2. Click on a cell in any open workbook, and you will see lines above and to the left of that cell

Or, if you added the code to a different workbook (not the Personal Workbook), open the file with your code.

Remember -- using this macro will prevent you from using the Undo feature.

More Tutorials

Worksheet VBA

Worksheet Tips

Hide Sheets Based on Tab Colour

Worksheet Protection Selector

Worksheet AutoFilter VBA

Copy VBA to Sheet

VBA - Get Started