Excel 2013 Conditional Formatting with AND operator

Question

I have an issue while trying to apply conditional formatting to a range of cells in Excel 2013.

My formular is

=AND(INDIRECT("N"&ROW()) >-60,INDIRECT("N"&ROW()) <-30)

and this applies to column C. The value in column N of the row is -35 (negative 35). This results in no formatting being applied. However if I apply the individual tests seperatly both evaluate to true and the formatting is applied.

=INDIRECT("N"&ROW()) >-60

=INDIRECT("N"&ROW()) <-30

Both the above work ok seperatly.

If I reference an individual cell then the AND operator works fine

=AND($N$27 >-60,$N$27 <-30)

Does anyone know what i'm doing wrong and how to apply the above condition to a range using INDIRECT and ROW please or a better method?

Thanks

Answer 1

Using INDIRECT() is unnecessary complicated.

Use =AND($N27 >-60,$N27 <-30) (note the removed $ before the row number!), and Excel will apply it respectively to all cells - each cell will its corresponding row. When you type it in, use the row number of the active cell; Excel is clever enough to apply it realtive to that.