In a complex sheet with lots of data, it’s sometimes helpful if you can highlight the active cell’s corresponding row or column — or both. Doing so seems like a complex problem, but it’s easier than you might think. I’ll use VBA’s ActiveCell property to return the row and column of the active cell. Then, I’ll build a conditional format rule that relies on that value for formatting.
The Excel demo files (.xls and .xlsm) contain the simple data set shown in Figure A. You can work with any sheet you like. As you can see in the Formula bar, F3 and G3 are literal values, not expressions. Those values are the result of a bit of VBA magic and not values that you’ll enter manually. That’s what we’ll tackle first.
We’ll add a highlighting feature to this simple data set.
Return the active row and column
Cells F3 and G3 are named ranges. Using the labels in F2 and G2, you can create those named ranges as follows:
- Select F2:G3. In Excel 2003, skip to step 3.
- Click the Formulas tab.
- In the Defined Names group, click Create from Selection. In Excel 2003, choose Name from the Insert menu, and select Create.
- In the resulting dialog, make sure Top Row is checked (the default), and click OK.
You now have two range names, SelRow in F3 and SelCol in G3. Although Figure A shows values in these cells, if you’re following along, they’ll be empty. Now, let’s create a VBA procedure to fill them:
- Press [Alt]+[F11] to open the Visual Basic Editor (VBE).
- In the Project Explorer, double-click the appropriate sheet object (if necessary) to open the sheet’s module. In this case, that’s Sheet 1 (ActiveCellHighlight).
- Enter the procedure shown in Listing A (Figure B).
- Return to the sheet.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) ‘Return active row or column in named cells. [SelRow] = ActiveCell.Row [SelCol] = ActiveCell.Column End Sub
This procedure will update the active row and column values in SelRow and SelCol, respectively.
For the procedure to work in the latest versions of Excel, you must save the file as a macro-enabled file (this isn’t necessary in 2003):
- Click the File tab. In Office 2007, click the Office button.
- Choose Save As in the left pane.
- In the resulting dialog, choose Excel Macro-Enabled Workbook (.xlsm) from the Save As drop-down menu. Enter a name if you haven’t already.
- Click Save.
At this point, F3 and G3 should display the row and column values, respectively, for the active cell. Because we used the SelectionChange procedure, this value will update automatically, every time you change the active cell. You might have expected an alpha character for the column, but don’t worry — the integer value you see is correct.
Apply the conditional format
Now, you’re ready to apply the format rules that will do the highlighting. There are two — one highlights the row and one highlights the column. To highlight the active row, do the following:
- Select the data range. In this case, that’s B3:D12. In Excel 2003, skip to step 3.
- Click the Home tab.
- In the Styles group, click Conditional Formatting. In Excel 2003, choose Conditional Formatting from the Format menu and skip to step 6.
- From the resulting menu, choose New Rule.
- In the top pane, select Use a formula to determine which cells to format.
- In the lower pane, enter the formula =ROW(B3)=SelRow (Figure C). In Excel 2003, choose Formula is from the Condition 1 drop-down menu and enter the same formula.
- Click the Format button.
- Click the Fill tab. In Excel 2003, click the Patterns tab.
- Choose a color and click OK twice. Figure D shows the new rule highlighting the active row within the data range.
Notice that the value in F3 (SelRow) is 6 and the new rule highlights row 6 — that’s exactly what we want. To add the column highlight, repeat the steps above, but in step 6, enter the expression =COLUMN(B3)=SelCol instead of the ROW() expression (Figure E).
Add the column highlight.
Put it to use
You can use only one rule or combine them as I’ve done. Most likely, you’ll want to locate the two named ranges SelRow and SelCol in an out-of-the-way spot. This technique is great for dashboards because it’s easy to implement and very effective.
Send me your question about Office
I answer readers’ questions when I can, but there’s no guarantee. When contacting me, be as specific as possible: For instance, “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. I’m not reimbursed by TechRepublic for my time or expertise, nor do I ask for a fee from readers. You can contact me at email@example.com.