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.
Article overview:
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
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.
