Forum Discussion
Conditional formatting formula evaluation is inconsistent
I have the following conditional formatting formulas:
1. INDIRECT(ADDRESS(ROW(), COLUMN() - 5))<>INDIRECT(ADDRESS(ROW(), COLUMN()))
2. AND(TRUE,TRUE)
3. AND(INDIRECT(ADDRESS(ROW(), COLUMN() - 5))<>INDIRECT(ADDRESS(ROW(), COLUMN())),INDIRECT(ADDRESS(ROW(), COLUMN() - 5))<>INDIRECT(ADDRESS(ROW(), COLUMN())))
#1 and #2 work, #3 does not (simply does not highlight anything), however #3 should work given that #1 and #2 work (#3 is literally AND over 2 copies of #1). What gives? How can I write a conditional formatting formula that uses AND() with comparisons involving INDIRECT(ADDRESS(...))?
2 Replies
It is not recommended to use AND and OR in conditional formatting, use * and + instead. Sometimes AND/OR don't work, looks like your case.
Conditional formatting rule formula could be
=( INDIRECT(ADDRESS(ROW(), COLUMN() - 5) ) <> INDIRECT( ADDRESS(ROW(), COLUMN()) ) )*(INDIRECT(ADDRESS(ROW(), COLUMN() - 5))<>INDIRECT(ADDRESS(ROW(), COLUMN() )) )when it works
nathlrowe Can't explain why the combination of AND, INDIRECT and ADDRESS doesn't work in conditional formatting. You can use them together in worksheet formulas, though your first and third one would need to be entered in a cell of their own, for instance in the next cell on the same row, referencing COL()-6 and COL()-1. Otherwise you get a circular reference.
Anyhow, you can use another rule to achieve what I believe you have in mind. See attached.
If that's not it, please clarify your intentions with some screenshots and/or a link to your file on OneDrive, Dropbox or similar.
