Forum Discussion
conditional formatting doesn't work with complex "IF" statement
=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
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;
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)But wouldn't be much easier to create a rule of type 'Format only cells that contain' with Cell Value, greater than, and =1 ?
Yes, definitely much easier. captainFlow mentioned this was for demonstration purposes. Maybe he can share his goal?
What do you want to do? Highlight the active cell?
- Yes, I want to highlight all cells for which condition will be true.
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.
