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

HansVogelaar , 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.

Patrick2788 

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

captainFlow 

I fear you're chasing rainbows...

HansVogelaar 
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.

captainFlow 

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

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.

captainFlow 

Let's say you want to apply the conditional formatting to G1:K100.

Select this range. G1 should be the active cell in the selection.

Use the following formula in the conditional formatting rule:

=OR(G1="";IF(ROW(G1)=1;G1>=1,1*$D$2;OFFSET(G1;-1;0)>=1,1*$D$2))

=ИЛИ(G1="";ЕСЛИ(СТРОКА(G1)=1;G1>=1,1*$D$2;СМЕЩ(G1;-1;0)>=1,1*$D$2))

You may have to adjust the multiplication factor 1,1

HansVogelaar 

It should return 0 if cell is empty.
If it is cell from the 1st row it should return 1 if cell's value is X percent more than the value in $D2 (for example) cell and 0 otherwise.
If it is cell from non the 1st row it should return 1 if value from the current cell is X percents more that value from the cell from the previous row but the same column.

Thanks for your help. I appreciate it.

captainFlow 

Yes: > What I want my formula does?

HansVogelaar 
It may be incorrect after I translated in English, because I wrote it in Russian and I use Russian for all formulas I write because it it local language for my document. Notice, please, that this formula works fine when I put it as fomula into the cell. I face with troubles during putting it in the formula for Conditional Formatting.

Formula doesn't have accurate bounds, so I can't say the range. I want it will work for many colums. But this formula contain one absolute cell adress.

What do you means with conditions? What I want my formula does?

captainFlow 

Your formula is syntactically incorrect, and I suspect it is far more complicated than necessary.

What is the range you want to format?

And what are the conditions?

HansVogelaar I spent few minutes for searchin this error meaning but found nothing usefull. I use INDIRECT(ADRESS(ROW();COLUMN())) - only for getting current value during Conditional Formatting evaluations, so it seems complex but I don't know how to make it in a different way.

I also tried to use CELL("value") function, but it behaves strange, seems like it doesn't get each cell's value for each evaluation, but gets ONE value for all evaluations for Conditional Formatting within current range.

captainFlow 

I don't know why it causes an error, but it does. In the Immediate window of the Visual Basic Editor:

Debug.Print Evaluate("=INDIRECT(ADRESS(ROW(),COLUMN()))")
Error 2029