Sample files: Use Excel’s conditional formatting to highlight the same row and column as the active cell
These files demonstrate the technique described in the article How to use Excel’s conditional formatting to highlight the same row and column as the active cell.
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.
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
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.
Subscribe to the Developer Insider Newsletter
From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays