The article How to highlight the active row, column, or both using VBA and conditional formatting combines a little VBA with conditional formatting to highlight the active row and column. Doing so is a visual asset that allows the user to see dependent or related values in a busy sheet. As you might imagine, there are lots of ways to vary this technique. In this article, I’ll show you a solution that highlights a single cell, in relation to the selected cell, instead of selecting the entire row.

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.

The sample data set and setup

Figure A shows the example data set. It’s simple on purpose, but as you complete the example, you’ll see how valuable it could be in a busy spreadsheet. In this example, we’ll apply a conditional formatting rule that highlights the date in column C when the corresponding amount value in column E is the active cell. For instance, if you select E5, our conditional formatting will highlight C5. You can work with an ordinary range or a Table object—the downloadable .xlsx file contains both. Table objects aren’t supported by the .xls format.

Figure A

Let’s highlight the date in column C for the selected amount value in column E.

If you’re working in a Ribbon version, save the file as a macro-enabled file. 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. If you’ll want to add new records, consider converting the data set range to a Table object, which will extend the format when you add new rows of data. Because the Table object isn’t available in menu versions of Excel this technique isn’t efficient for them unless you’re working with a stable data set.

SEE: Build your Excel skills with these 10 power tips

Add the rule

When applying conditional formatting, you usually begin by identifying the cell or range you want to apply the conditional formatting to—in this case, that’s the date values in column C. To get started, select C5:C9, and then do the following:

1. Click the Home tab.
2. Click Conditional Formatting in the Styles group and then select New Rule from the dropdown list.
3. In the New Formatting Rule dialog, select Use A Formula to determine which cells to format in the top pane.
4. In the resulting Format Values Where This Formula Is True control, enter the following expression:
   =CELL("address")=CELL("address",$E5)
   The mixed reference $E5 ensures that the condition returns true only when the active cell is in column E.
5. Click the Format button.
6. At this point, you need to choose a format that’s distinguishable from the existing formatting. It’s up to you, but for this example, click the Fill tab and choose a bright contrasting color, such as orange.
7. Click OK. Figure B shows the formula rule and the format.
8. Click OK to return to the sheet.

Figure B



Specify the condition to meet and format.

The absolute reference to column E, $E, limits the expression to returning true only when the active cell is in column E. The row reference, 5, is relative; it’s the first row in the range you selected earlier (C5:C9). Consequently, you further limit the expression to returning true only when the active cell is in rows 5 through 9. In other words, the expression can return true only if the active cell is E5, E6, E7, E8, or E9.

If you select any of the amount values in column E, nothing happens—not yet. Let’s take a minute to understand why. The format rule uses the CELL() function, which requires a forced calculation to update. To the best of my knowledge, there is no sheet-level volatile function that will return the address of the active cell. To demonstrate this behavior, enter the following function into cell G5:

=CELL("address")

As you can see in Figure C, the function returns the cell address $G$5. That’s probably what you expected to see. Now, copy that function to cells G6:G9, as shown in Figure D. Now that’s probably not what you expected to see.

Figure C



The CELL() function returns the absolute address of the current cell.

Figure D



The function doesn’t update when you copy it.

You must force the sheet to recalculate by pressing F9. To demonstrate, select cell D12 and you’ll see that the CELL() functions don’t update. Figure E shows the result of selecting D12 and pressing F9—the CELL() functions in G5:G9 return $D$12, but only after pressing F9 to recalculate do the functions update.

Figure E



Recalculate the sheet to update the results of CELL().

The fix

For this conditional format rule to work, you must force the sheet to recalculate every time you change the selection. You can do so by using the worksheet’s SelectChange event to recalculate the worksheet. There’s a slight possibility that doing so will impact performance, but that won’t be a problem in most sheets—even busy ones. However, if it does happen to you, you’ll have to decide if the performance hit is worth the visual advantage the format supplies.

To add the code, press Alt+F11 to open the Visual Basic Editor (VBE). Using the Project Explorer pane on the left, choose the appropriate workbook and sheet. Enter the code shown in Listing A. Don’t copy and paste from this web page. Instead, enter the code manually or use the downloadable demonstration file. After adding the code, save your work and return to the sheet.

Listing A

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Target.Calculate

End Sub

After returning to the sheet, select any cell in E5:E9 and watch as the conditional format updates, as shown in Figure F. The CELL() functions you added earlier to column G aren’t necessary, but watching them update after adding the event procedure helps clarify the code’s necessity. The CELL() functions update when you change the selection only because you added the event procedure in Listing A.

Figure F



The conditional format works after adding the code in Listing A.

Stay tuned—we’ll review more conditional formats that depend on the active cell over the next few months.

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.

Also read…