In Excel, how can I dynamically highlight a row based on a cell whose value is the number of the row I want to highlight?

Question

In my Excel Spreadsheet, I have a table set up and want to be able to dynamically hightlight the table row that corresponds to the value in a cell.

For example, let's say my table spans cells A1:M13

Then, outside my table in, say, cell e17 (any cell outside the table), if I enter the number 4, I want all of row 4 in my table highlighted.

I know that I would need to configure Conditional Formatting, and create a New Rule..., and Use a formula to determine which cells to format. But, I'm having a hard time figuring out a formula to use.

I'm playing around with the ROW() and INDIRECT() functions, but not getting there.

Answer 1

You could have one defined cell e.g. A1 (highlightRow) (moving the table two rows down), name it, highlight it by a background color - and then use that value for the format condition: sth like = ROW() = highlightRow + 4