Resolved >>> Conditional formatting with INDIRECT and AND

I think Excel is doing something weird here.
I'm trying to conditionally format a cell based on the cells around it.
There might be a better way to do this but this is what I have done.
I'll just show some trials of formatting conditions I've done.

Please Login or Register  to view this content.

It seems that any time I try to use INDIRECT and a logic function in the same condition the formatting is just ignored. I've tried many variations (not(OR) statements etc) but can't seem to sort it out.
I can't seem to find any info on this problem which suggests I'm solving the problem in a weird way.

Any comments or suggestions?
Thanks,
Mike

Just in case anyone else wants to play with this.

Please Login or Register  to view this content.

Am I missing something obvious here?

What are you trying to do exactly?

If you want to format a range of cells whenever the cell to the right is >0 (or equal to TRUE, I'm not quite sure) then you shouldn't need ADDRESS or INDIRECT. If you use a formula for the active cell which refers to the cell immediately to the right then all other cells will adjust automatically, e.g.

If you want A1:A10 to be red if the corresponding cell in B1:B10 is >0

Select A1 and extend selection to A10

use conditional formatting with the formula

=B1>0

format as required

Thanks, you're right.
It's quite a complicated truth condition, but your approach works, I just got wrapped up in worksheet functions.

But still, any ideas why the functions above don't work?
(I can dream up some contrived scenarios where you might need it)

Cheers,
Mike

In what way don't they work?

With this, for instance

=AND(INDIRECT(ADDRESS(ROW(),COLUMN()+1))>0,TRUE)

If you use this in A1 then it returns TRUE if B1>0, isn't that what you expect?

It should always give the same result as

=INDIRECT(ADDRESS(ROW(),COLUMN()+1))>0

Sorry was a bit unclear there.
The problem is that the truth statement seems to evaluate correctly but this does not trigger the conditional formatting.
I've attached a screenshot and sample workbook.
Thanks,
Mike

Hello Mike,

Yes, it does look a bit odd. I don't think I can explain it absolutely but I imagine it has to do with the way OFFSET works. OFFSET returns a range so looking at the value of a range doesn't really make sense. Of course, here, that range is just a single cell but even so you probably should enclose OFFSET in a SUM function. Doing this makes the conditional formatting work, I think.

Nice,
Didn't think of that.
Thanks for looking into it.
Mike