Contextures

Home > Macros > Basics > Active Cell

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.

active cell is highlighted

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.

active cell is highlighted

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

view code in personal workbook

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. download link in comment

  4. 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

paste code into personal workbook

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.

Excel Training by Mynda

For video-based lessons on working with Excel data, I recommend these highly-rated courses, offered by Mynda Treacy, at her website, My Online Training Hub.

NOTE: These are affiliate links, and I will earn a small commission if you purchase a product through these links.

  1. Excel Power Query Course -- If you can only take one course to get your Excel skills to an advanced level, I recommend this Power Query course. You'll learn how to pull data from almost anywhere, clean it up, if needed, and use it as a source for pivot tables, formula lookups, and other time-saving uses.
  2. Excel Dashboard Course -- Mynda is a dashboard expert, and this highly-rated course will help you become one too! I've been through this course, and highly recommend it, for anyone who's skilled at Excel, and wants to quickly improve their dashboard game.
  3. Excel Power Pivot Course -- If you're a pivot table pro, take your skills to the next level, with this Power Query course. The short video below gives you a quick preview of what you'll learn in the course. There's lots to learn, including DAX, a new formula language. But don't worry - Mynda walks you through it with clear explanations.

More Macro Tutorials

Worksheet VBA

Worksheet Tips

Hide Sheets Based on Tab Colour

Worksheet Protection Selector

Worksheet AutoFilter VBA

Copy VBA to Sheet

VBA - Get Started

 

 

About Debra

 

 

Last updated: March 14, 2023 7:30 PM