Excel conditional formatting not working as I want

Question

I define an 8x8 grid of cells, B6:I13. Two other cells outside this grid contain a row (D3) and column (E3) value from 0 to 7 and represent the cell in the table I wish to format. I select the entire grid and open the Conditional Formatting dialog, select New Rule, and then create a rule based upon a formula. I enter the formula

=AND(ROW(B6)-6=$D$3, COLUMN(B6)-2=$E$3)

and then apply the desired formatting. I click the OKs to close the dialog and nothing happens. The cell referred to is not formatted. Why not? Obviously the formula isn't returning true but what is happening to make that the case? I assume Excel will automatically adjust the cell in the ROW and COLUMN calls for each cell in the grid but maybe it doesn't.

Answer 1

You didn't quite get your formula right, it's off by one, should be:

=AND(ROW(B6)-5=$D$3, COLUMN(B6)-1=$E$3)

And if it doesn't work for you I'm not sure what you are doing wrong, as it works as it should: