Baffled by the behavior of conditional formatting in excel

Question

I would like to conditionally format rows where Column C has a 9, and Column B is greater than Column A. The query

=AND(ISNUMBER(SEARCH("9",INDIRECT("c"&ROW()))),INDIRECT("b"&ROW())>INDIRECT("a"&ROW()))

doesn't work for me. If I type this as a formula in the sheet, it behaves as expected. However, for conditional formatting it doesn't work.

The simpler query

=AND(ISNUMBER(SEARCH("9",INDIRECT("c"&ROW()))),1=1)

doesn't work either. Again it works as a formula in the sheet, but not for conditional formatting.

The even simpler query =AND(2=2,1=1) works.

Any insights will be much appreciated.

Answer 1

Select ColumnsA:C and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true::

=AND(SEARCH(9,$C1),$B1>$A1)

Format..., select choice of formatting, OK, OK.

If you want more than the first three cells of each row formatted, where conditions are met, extend the Applies to range.