Selected Answer

Indeed it is conditional formatting that will do the job, meaning the formatting must be applied to the cells whose colour is supposed to change - a much bigger range than the one indicated in AP3, comprising all cells which might, at one time or another, be included in the range specified in AP3. Determine the cells to be formatted by the formula below.

`=AND(AND(ROW()>=INDEX(ROW(INDIRECT($AP$3)),1),ROW()<(INDEX(ROW(INDIRECT($AP$3)),1)+ROWS(INDIRECT($AP$3)))),AND(COLUMN()>=INDEX(COLUMN(INDIRECT($AP$3)),1),COLUMN()<(INDEX(COLUMN(INDIRECT($AP$3)),1)+COLUMNS(INDIRECT($AP$3)))))`

Basically, what this formula says is this. Apply the colour if ...

- A cell's row number >= the first row of the range defined in AP3

AND the cell's row number <= the last row of the range defined in AP3

AND
- A cell's column number >= the first column of the range defined in AP3

AND the cell's column number <= the last column of the range defined in AP3.

It follows that the formula is laid out to permit AP3 to specify a range comprising multiple coloumns as well as multiple rows. Note that it isn't necessary to apply absolute references in AP3. B10:B17 will do the job as well as $B10:$B17 or $B$10:$B$17.

As a matter of interest, in a worksheet cell, =Row(B10:B17) will return the number 10, being the first row of the defined range. CF didn't accept this however, presumably because Row(B10:B17) actually is an array {10;11;12;13;14;15;16;17}. Therefore the above formula specifically extracts the first element of that array, using INDEX(ROW(B10:B17),1). Convoluted but necessary.