Conditional formatting not working with AND

  • Thread starter Thread starter Khuli
  • Start date Start date
K

Khuli

I have some conditional formatting to check that a value in column CO
does not match the value in column A, and that cell should not be
blank.

If I check against column A using
=CO31<>OFFSET(CO31,0,1-COLUMN(CO31))
it works fine.

If I check the cell isn't blank using
=CO31<>""
it works fine.

BUT if I combine both and use
=AND(CO31<>OFFSET(CO31,0,1-COLUMN(CO31)), CO31<>"")
then it doesn't work.

Anyone have any ideas? Thanks.
 
Excellent question. It should work but it doesn't. Fortunately the less
job protecting way of writing the formula does work ;-)

=AND(CO31<>A31,CO31<>"")

You wrote it the way you did to avoid a circ? No need.
 
Actually I wrote it that way because sections of the spreadsheet get
new cells added.

If you add a new cell above CO31, pushing it down to CO32, the
conditional format formula becomes:

=AND(CO32<>A31,CO32<>"")

which is no longer correct.

Thanks for confirming that the formula I had really ought to work
though!
 
Back
Top