Menu
My problem is about programming in excel (create a formula for conditional formatting rule).
If i use the following formula in my format condition formula:
=INDIRECT("A"&ROW())>1
And apply on all rows include first row with following range in Apply to field:
$A:$F
Result: It works well.
But i want apply it to all rows except first row. So i changed it to:
=AND(INDIRECT("A"&ROW())>1;ROW()>1)
Result: Now it did not work on any row.
What is my mistake in the above formula?
315
Select the column, or rows that you intend to apply the conditional formatting to. Go to Conditional Formatting>Manage Rules. Click the New Rule button in the rules manager and from the list of conditions, select 'Format only cells that contain' and select 'Blank' under the 'Format only cells with' dropdown. Click OK.
The following selection arrow appears to indicate that clicking selects the row. You can click the first cell in the table row, and then press CTRL+SHIFT+RIGHT ARROW. Click the upper-left corner of the table. The following selection arrow appears to indicate that clicking selects the table data in the entire table.
Select the header or the first row of your list and press Shift + Ctrl + ↓(the drop down button), then the list has been selected except the first row.
First, select the cell where conditional formatting is already applied. After that, go to the Home tab and click on the Format Painter icon. Now, just select the cells from multiple rows. Conditional formatting will be automatically applied to all the cells.
Just add an extra formula rule to the row(s) you would like to exclude.
Set the formula to TRUE and tick the Stop If True option.
This rule should be on top of the rule list.
62
At first your question is confusing. You can "apply a conditional formatting rule to all rows except first row" by applying it not to $A:$F but to $A$2:$F$1048576.
But I suspect you want apply it to all rows in columns $A:$F but it shall then exclude row 1 while testing conditions.
If so your problem is the volatile behavior of INDIRECT. But INDIRECT is not needed here. Your first condition can also be written as:
=($A1>1)
This is because even conditional formatting will respect difference between fixed and variable cell references by using $ or omitting $. So this formula applied to $A:$F will check whether $A1>1 in first row, $A2>1in second row, $A3>1in third row and so on. This is because the column $A is fixed using $ and so is absolute reference to column A but the row number 1 is not fixed and so is a relative reference to the actual row..
Applied to $A:$F
this will have the same behavior as your INDIRECT formula, which is: Do Format current row if value of first cell in this row is greater than 1:
Then to exclude the first row is possible using:
=AND($A1>1,ROW()>1)
One of the disadvantages while using relative cell references in conditional formatting is that they must be changed if the range on which it is applied changes. For example in this case if someone inserts a row above row 1.
But even then INDIRECT is not necessary. Then we can use INDEX - MATCH:
=(INDEX($A:$A,ROW())>1)
and
=AND(INDEX($A:$A,ROW())>1,ROW()>1)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
