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

Copper Contributor

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

30 Replies

@captainFlow 

What do you want to do? Highlight the active cell?

Yes, I want to highlight all cells for which condition will be true.

@captainFlow 

If you want to highlight cells whose value is is a number greater than 1:

  • Select the range that you want to format.
  • In the following, I will assume that A2 is the top left cell of the selection, and that A2 is the active cell in the selection.
  • On the Home tab of the ribbon, click Conditional Formatting > New Rule...

  • Select 'Use a formula to determine which cells to format'.

  • Enter the formula

=AND(ISNUMBER(A2),A2>1)

  • Click Format...
  • Activate the Fill tab.
  • Select a highlight color.
  • Click OK, then click OK again.
Thanks, but my formula is much more complex that I attached here and when I was dubugging it I found that IF with nested expressions doesn't work for me, so I need to deal with complex exressions using IF.

@captainFlow 

If you want us to help you, you will have to tell us what you want - in detail.

For now I want to know, why the formula (=IF(INDIRECT(ADRESS(ROW();COLUMN()))>1;1;0)) doesn't work when I use it as a formula for conditional formatting.

@captainFlow 

It's because the formula evaluates to an error in Conditional Formatting.

So it'd be useful to know what you want to accomplish.

So, can you say why it causes an error (=IF(INDIRECT(ADRESS(ROW();COLUMN()))>1;1;0)) ? I use Conditional Formatting for rows with numbers only. Notice, that I use exactly the same formula for cell (not formatting, just for evaluates) and it works fine as well as using this formula in google sheets for CF and just cell's formula.

@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

@captainFlow 

This might work. I see you're using semi-colons so I've written the formula using those as the list separator.  I believe the issue with the other formula is the use of ADDRESS.  

=IF(INDIRECT("RC";0)>1;1;0)

 

@Patrick2788 

But wouldn't be much easier to create a rule of type 'Format only cells that contain' with Cell Value, greater than, and =1 ?

 

S1897.png

@Hans Vogelaar 

Yes, definitely much easier. @captainFlow mentioned this was for demonstration purposes. Maybe he can share his goal?

Hi Patrick
Yes, you are right. Here is full formula I need.

=IF(INDIRECT(ADRESS(ROW();COLUMN())) > 0; IF((ROW()=1); INDIRECT(ADRESS(ROW();COLUMN())) >= ($D2*1,1); INDIRECT(ADRESS(ROW();COLUMN())) >= INDIRECT(ADRESS(ROW()-1;COLUMN()))*1,0); 0)

I created the tickets after found out that nested IF statement doesn't work for me.

UPD. To be more correct, using INDIRECT function inside of IF doesn't work for me.

UPD.2. The thing is I've written this formula for google sheets for Conditional Formatting and it works fine. I can't find out why it doesn't work for excel in microsoft365

@Hans Vogelaar 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 

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?

@Hans Vogelaar 
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 

Yes: > What I want my formula does?

@Hans Vogelaar 

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 

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