Baffled by the behavior of conditional formatting in excel

0
votes

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.

1
excelconditional-formatting
You do not need the indirect =AND(ISNUMBER(SEARCH("9",$C1)),$B1>$A1) where the 1s are the first row in your applies to reference. – Scott Craner

1 Answers

0
votes

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.