Highlighting the entire row and column for the active cell in a complex sheet could prove helpful for quickly reviewing dependent or related values. Fortunately, adding a conditional formatting rule to do so is easier than you might think. You'll need two expressions and a simple VBA procedure. As you might imagine, there are lots of ways to vary this technique. The earlier article How to highlight the active row, column, or both using VBA and conditional formatting uses the same technique, but a different expression.
I'm using Excel 2016 on a Windows 10 64-bit system, but this technique will work in older versions. Because the format depends on code, this technique won't work in the browser edition. For your convenience, you can download the .xlsm or .xls demonstration file.
About the functions
The formulas used in the conditional rules are simple. The ROW() and COLUMN() functions return the row and column number of a reference. For instance, ROW(B4) returns 4 and COLUMN(B4) returns 2. If the reference isn't explicit, both functions assume the row or column in which the function appears. Both ROW() and COLUMN() will return 4 and 2, respectively, if in B4.
Excel's CELL() function returns information—format, location, content—about a cell. This function uses the syntax
CELL(info,[reference])
where info is a text value that specifies the type of cell information to return and reference identifies the cell. If you omit reference, CELL() evaluates the last cell that was changed.
SEE: Microsoft Power BI: Getting started with data visualization (TechRepublic)
Now, let's enter these functions and see how they work. To do so, enter ROW() in B3:B6, enter COLUMN() in D3:G3. Figure A shows the initial results. If you enter CELL("row") and CELL("col") in B3:B6 and D3:G3, respectively, you'll see a different result.
Figure A
Enter the functions.
The conditional format expressions we'll use compare the results of these functions as follows:
=ROW()=CELL("row")
=COLUMN()=CELL("col")
Figure B shows the results of entering the row expression in B3:B6 and the column expression in D3:G3. These expressions will return TRUE only if the active cell is in rows 3, 4, 5, and 6 or columns D, E, F, and G. Initially, the expressions in B3 and D3 return TRUE because the active cell is in row 3 and column D. If you select C7 and press F9 (to recalculate), the expressions return FALSE because the active cell is in neither range. Click C4 and press F9; the expression in B4 returns TRUE because the active cell is in one of the evaluated rows (4). Click D4 and press F9; the expressions in B4 and D4 both return TRUE because the active cell is in both ranges.
Figure B
Enter the conditional rule expressions.
Apply the rules
The first step to applying this conditional format is to enter these two rules. Because we want to highlight the entire row and column, you must select the entire sheet by pressing Ctrl+A or clicking the Selection cell (the gray cell in the top-left corner were the column and row header cells intersect). With the entire sheet selected, do the following:
- Click the Home tab and choose New Rule from the Conditional Formatting dropdown in the Styles group.
- In the resulting dialog, click Use A Formula To Determine Which Cells To Format in the top pane.
- In the lower pane, enter the expression
=ROW()=CELL("row") - Click Format, click the Fill tab, choose a color, and click OK. Figure C shows the expression and the format.
- Click OK.
Figure C
Enter the expression and choose a format.
If necessary, select the entire sheet again. To enter the column format, repeat the above steps, but enter the expression =COLUMN()=CELL("col") to accommodate the column.
Initially, the formatting rules highlight row 1 and column A. If you move the active cell, nothing changes. We need to recalculate the sheet because the expressions we used aren't volatile—we must force them to recalculate. To illustrate this behavior, select E6—nothing changes. Press F9 to recalculate the sheet and the highlight updates accordingly, as shown in Figure D.
Figure D
Recalculate to update the highlight.
Add the code
Most likely, you'll want to avoid pressing F9 to update; this technique needs a simple procedure that recalculates the sheet every time you change the active cell.
If you're working in a Ribbon version, save the workbook as a macro-enabled file before you continue. To do so, click the File tab, choose Save As, (name the file if necessary), select Excel Macro-Enabled Workbook (*.xlsm) from the File Type dropdown, and click Save.
To enter the VBA procedure, press Alt+F11 to launch the Visual Basic Editor (VBE). If necessary, use the Project Explorer to make sure you're working in the right sheet and enter the procedure shown in Listing A.
Listing A
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Target.Calculate End Sub
Return to the sheet and move the active cell as often as you like. Thanks to the procedure you added, the highlight updates automatically.
If you want to limit the highlight to the data set, select the data set instead of the entire sheet before you enter the conditional formatting rules. This format works equally as well with a Table object.
Send me your question about Office
I answer readers' questions when I can, but there's no guarantee. Don't send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at susansalesharkins@gmail.com.
