Contents
You have a big monitor, but the tables you work with are even bigger. And, looking over the screen in search of the necessary information, there is always a chance to “slip” your eyes to the next line and look in the wrong direction. I even know people who, for such occasions, always keep a wooden ruler close to them to attach it to the line on the monitor. Technologies of the future!
And if the current row and column are highlighted when the active cell moves across the sheet? A kind of coordinate selection like this:
Better than a ruler, right?
There are several ways of varying complexity to implement this. Each method has its pros and cons. Let’s take a look at them in detail.
Method 1. Obvious. Macro that highlights the current row and column
The most obvious way to solve our problem “on the forehead” – we need a macro that will track the change in the selection on the sheet and select the entire row and column for the current cell. It is also desirable to be able to enable and disable this function if necessary, so that such a cross-shaped selection does not prevent us from entering, for example, formulas, but only works when we look through the list in search of the necessary information. This brings us to the three macros (select, enable, and disable) that will need to be added to the sheet module.
Open a sheet with a table in which you want to get such a coordinate selection. Right-click on the sheet tab and select the command from the context menu Source text (Source Code).The Visual Basic Editor window should open. Copy this text of these three macros into it:
Dim Coord_Selection As Boolean 'Global variable for selection on/off Sub Selection_On() 'Macro on selection Coord_Selection = True End Sub Selection_Off() 'Macro off selection Coord_Selection = False End Sub 'Main procedure that performs selection Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim WorkRange As Range If Target.Cells.Count > 1 Then Exit Sub 'if more than 1 cell is selected, exit If Coord_Selection = False Then Exit Sub 'if selection is off, exit Application.ScreenUpdating = False Set WorkRange = Range(" A6:N300") 'address of the working range within which the selection is visible Change the address of the working range to your own – it is within this range that our selection will work. Then close the Visual Basic Editor and return to Excel.
Press the keyboard shortcut ALT + F8to open a window with a list of available macros. Macro Selection_On, as you might guess, includes coordinate selection on the current sheet, and the macro Selection_Off – turns it off. In the same window, by clicking the button Parameters (Options) You can assign keyboard shortcuts to these macros for easy launch.
Advantages of this method:
- relative ease of implementation
- selection – the operation is harmless and does not change the content or formatting of the sheet cells in any way, everything remains as it is
Cons of this method:
- such selection does not work correctly if there are merged cells on the sheet – all rows and columns included in the union are selected at once
- if you accidentally press the Delete key, then not only the active cell will be cleared, but the entire selected area, i.e. delete data from the entire row and column
Method 2. Original. CELL + Conditional Formatting Function
This method, although it has a couple of drawbacks, seems to me very elegant. To implement something using only the built-in Excel tools, minimally getting into programming in VBA is aerobatics 😉
The method is based on using the CELL function, which can give a lot of different information on a given cell – height, width, row-column number, number format, etc. This function has two arguments:
- a code word for the parameter, such as “column” or “row”
- the address of the cell for which we want to determine the value of this parameter
The trick is that the second argument is optional. If it is not specified, then the current active cell is taken.
The second component of this method is conditional formatting. This extremely useful Excel feature allows you to automatically format cells if they meet specified conditions. If we combine these two ideas into one, we get the following algorithm for implementing our coordinate selection through conditional formatting:
- We select our table, i.e. those cells in which the coordinate selection should be displayed in the future.
- In Excel 2003 and older, open the menu Format – Conditional Formatting – Formula (Format — Conditional Formatting — Formula). In Excel 2007 and newer – click on the tab Home (Home)button Conditional Formatting – Create Rule (Conditional Formatting — Create Rule) and choose the rule type Use a formula to determine which cells to format (Use formula)
- Enter the formula for our coordinate selection:
=OR(CELL(“row”)=ROW(A2),CELL(“column”)=COLUMN(A2))
=OR(CELL(«row»)=ROW(A1),CELL(«column»)=COLUMN(A1))
This formula checks to see if the column number of each cell in the table is the same as the column number of the current cell. Likewise with columns. Thus, only those cells that have either a column number or a row number that matches the current cell will be filled in. And this is the cross-shaped coordinate selection that we want to achieve.
- Нажмите кнопку Framework (Format) and set the fill color.
Everything is almost ready, but there is one nuance. The fact is that Excel does not consider a change in the selection as a change in the data on the sheet. And, as a result, it does not trigger recalculation of formulas and recoloring of conditional formatting only when the position of the active cell changes. Therefore, let’s add a simple macro to the sheet module that will do this. Right-click on the sheet tab and select the command from the context menu Source text (Source Code).The Visual Basic Editor window should open. Copy this text of this simple macro into it:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) ActiveCell.Calculate End Sub
Now, when the selection changes, the process of recalculating the formula with the function will be launched CELL in conditional formatting and flood the current row and column.
Advantages of this method:
- Conditional formatting does not break custom table formatting
- This selection option works correctly with merged cells.
- No risk of deleting an entire row and column of data on accidental click Delete.
- Macros are minimally used
Cons of this method:
- The formula for conditional formatting must be entered manually.
- There is no quick way to enable/disable such formatting – it is always enabled until the rule is deleted.
Method 3. Optimal. Conditional Formatting + Macros
Golden mean. We use the mechanism for tracking the selection on the sheet using macros from method-1 and add safe highlighting to it using conditional formatting from method-2.
Open a sheet with a table in which you want to get such a coordinate selection. Right-click on the sheet tab and select the command from the context menu Source text (Source Code).The Visual Basic Editor window should open. Copy this text of these three macros into it:
Dim Coord_Selection As Boolean Sub Selection_On() Coord_Selection = True End Sub Sub Selection_Off() Coord_Selection = False End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim WorkRange As Range, CrossRange As Range Set WorkRange = Range("A7:N300") 'адрес рабочего диапазона с таблицей If Target.Count > 1 Then Exit Sub If Coord_Selection = False Then WorkRange.FormatConditions.Delete Exit Sub End If Application.ScreenUpdating = False If Not Intersect(Target, WorkRange) Is Nothing Then Set CrossRange = Intersect(WorkRange, Union(Target.EntireRow, Target.EntireColumn)) WorkRange.FormatConditions.Delete CrossRange.FormatConditions.Add Type:=xlExpression, Formula1:="=1" CrossRange.FormatConditions(1).Interior.ColorIndex = 33 Target.FormatConditions.Delete End If End Sub Don’t forget to change the working range address to your table address. Close the Visual Basic Editor and return to Excel. To use the added macros, press the keyboard shortcut ALT + F8 and proceed in the same way as method 1.
Method 4. Beautiful. FollowCellPointer add-on
Excel MVP Jan Karel Pieterse from the Netherlands gives away a free add-on on his website FollowCellPointer(36Kb), which solves the same problem by drawing graphic arrow lines using macros to highlight the current row and column:
Nice solution. Not without glitches in places, but definitely worth a try. Download the archive, unpack it to disk and install the add-on:
- in Excel 2003 and older – through the menu Service – Add-ons – Overview (Tools — Add-Ins — Browse)
- in Excel 2007 and later, through File – Options – Add-ons – Go – Browse (File — Excel Options — Add-Ins — Go to — Browse)
- What are macros, where to insert macro code in Visual Basic