conditional formatting doesn't work with complex "IF" statement

[captainFlow]

=IF(INDIRECT(ADRESS(ROW();COLUMN()))>1;1;0)
It doen't work, when I put it in the formula for conditional formatting, but for example when I try to separate it and use "INDIRECT(ADRESS(ROW();COLUMN()))>1" or simple "IF" statement like "=IF(ROW()>1;1;1)" (it isn't exactly what I want, it is just for demonstration)

UPD. The formula works fine in google sheets. I have troubles only using excel from microsoft 365.

[#2]

Thanks

But do we have ability to solve it without references such as G1 ? Do we have any function like ROW() or COLUMN() to get value for each cell during calculations?

[#3]

@captainFlow 

The reference to G1 in my example is relative. Excel will automatically adjust it for the other cells in the selected range. So when evaluating the conditional formatting rule for cell G2, it will use G2 in the formula instead of G2, and when evaluating the rule for cell H1, it will use H1 instead of G1, etc.

So you only have to refer to the top left cell of the selected range, and Excel will take care of the rest for you.

[#4]

Sure, I know about relatives field functionality, but I want to make completly independet formula to make it usage much more simple.

[#5]

@captainFlow 

I'm afraid I don't know how to do that.

[#6]

@Hans Vogelaar 
One more thing that could help me is function like ROW or COLUMN, which returns value each cell during calculation. As I said before I found CELL function, but it doesn't work correctly for me.

[#7]

@captainFlow 

I fear you're chasing rainbows...

[#8]

@captainFlow 

Maybe try:

=IF(INDEX(INDIRECT(ADRESS(ROW();COLUMN())), 1)>1;1;0)

[#9]

The trade off with this is the reliance on volatile functions. Excel will re-evaluate any cells (and dependents) each time it re-calculates. This would be every cell containing the conditional formatting rule and any cells referred to in your formula. Additionally, conditional formatting formulas are single threaded. You can see why this might be a performance concern with a large workbook.
https://learn.microsoft.com/en-us/office/client-developer/excel/excel-recalculation

[#10]

@JMB17 

Hi
Yes, it really works. I've tryed handle it with 'index' function, but couldn't finish it because of lack of experience. Big thanks :]

P.S. Getting current cell value via code seems little strange in that way XD;

[#11]

@Patrick2788 

Hi
Thanks for your reply, initially my function isn't supposed for using in big books, but thanks for yours warnings.

[#12]

@Hans Vogelaar , @Patrick2788 Thanks for help, special thank to @JMB17 . This function really works for me, but one more thing you should think about is my formula became longer then 255 symbols, so I added function for getting current cell value to name manager via LAMBA function.