Use A Macro To Highlight The Active Cell
June 14, 2021 - by Bill Jelen
Challenge: Microsoft changed the selection highlighting in Excel 2007. If you select 20 cells, Excel highlights those cells in super-ultra-light blue. Imagine if you had 5 gallons of white paint and dropped in one drop of blue paint. That paint would have more color than the new selection color in Excel 2007. You want to highlight the active cell so it’s easier to spot.
Solution: There is a cool event macro that can add a splash of color to the selection and draw crosshairs to help you locate the row and column where the active cell is located. In Figure 141, bright yellow highlight indicates the active cell in D16. Lighter yellow is used to mark column D and Row 16. As you move the cell pointer, the highlights change. Figure 142 shows the crosshairs pattern for B20.
This macro runs every time you move to a new cell in the worksheet. The macro works for one worksheet.
Note: If you want it to work on all worksheets, put this code in the SheetSelectionChange macro in the ThisWorkbook code pane.
Follow the instructions in “Create an Event Handler Macro” to open the worksheet code module. Then paste in the following code:
Here is how the code works:
The first line indicates that you will have access to a range variable called
This is an object variable, so it not only tells you the value of the active cell (Target.Value or simply Target), it can tell you information about the active cell, such as Target. Row or Target. Column
The Dim and Set lines define an objet variable to refer to the current window in Excel:
While many macros refer to the current worksheet, you need to refer to the active window here so you can capture the top row in the visible portion of the worksheet.
This line resets the color of all cells to have no fill:
This erases all the yellow highlighting drawn in by the last running of the macro. Because you don’t specify which cells, this resets all cells in the entire worksheet.
This line changes the color of the selection to bright yellow:
It uses the old Excel 2003 concept of Colorlndex so that it will work in either Excel 2003 or Excel 2007. Excel 2007 supports more than 56 colors, so you can use the RGB function to return any of 16 million colors.
Next, you draw in the crosshairs in a lighter yellow. This involves looping from the top row in the visible section of the worksheet down to the row above the selection. To find the top row of the visible section of the worksheet, use wi.VisibleRange.Rows(1).Row. To find the row immediately above the selection, use Target. Row – 1. The following loop goes through each of the cells from the selection up to the top of the visible worksheet:
The line of code inside the loop colors the cell at the intersection of i and the same column as the Target. 36 is the color code for light yellow.
Only a minor adjustment is needed to build a second loop to color in all the cells in the current row from the left edge of the worksheet up to one column to the left of the selection:
You might be wondering what would happen if Target is the top row of the visible window. Say that you select cell A11 in Figure 142. The macro clears all the yellow formatting from all cells in the worksheet, resetting all cells back to their original color. The macro then colors the Target cell bright yellow. This colors A11.
The first line of the loop uses row 11 as the first row of the visible window. It uses row 10 as the row above the target cell. When the loop says For i = 11 to 10, Excel simply skips the loop. Nothing gets colored light yellow in the first loop. Similarly, the second loop is skipped as Excel tries to loop from 1 to 0.
Excel does a very quick and smooth job of running event handler macros run. As you move from cell to cell in the worksheet, Excel constantly redraws the yellow highlights to help you find the active cell.
Summary: An event handler macro can help you keep track of the active cell.
Title Photo: henry perks on Unsplash
This article is an excerpt from Excel Gurus Gone Wild.
