I have the following annoying problem with Excel 2010. I use Conditional formatting to apply bold formatting to each line of the spreadsheet that meets a condition expressed by a formula. In the "applies to", I write =$A$2:$Q$149, which is the current extent of my spreadsheet. But I want this range to grow with my spreadsheet, i.e., I want the formatting to permanently apply to the whole spreadsheet. Regularly, my "applies to" field is transformed into a complex range, that selects most lines of spreadsheet, but not all, like =$A$2:$Q$138;$A$140:$Q$147. Have you ever encountered such a problem? Is there a solution?
Asked
Active
Viewed 6.5k times
5 Answers
2
The solution is to use a dynamic defined range. I usually use the offset function. You can find some help here. When you have defined the dynamic range, you can use as a range in your conditional formatting.
When you click "Apply" and then "Ok", the formatting should be applied to that range.
If you return to the conditional formatting, you'll see that the dynamic range is actually translated to the row-column notation. However, I've noticed that if you add a row to your dynamic range (by filling in a blank cell), this is automatically propagated to the conditional formatting.
stedes
- 121
1
There is a nice workaround for it, which I found in the MrExcel.com forum: https://www.mrexcel.com/board/threads/dynamic-range-for-conditional-formating-on-shared-sheet.939723/#post-4516043
- Clear your existing rule.
- Select the entire sheet by clicking the triangle between 1 and A
- Click Conditional Formatting > New > Rule > Use a formula > and enter this formula:
=AND($G1=Guide!$A$7,ROW()>1,ROW()<=6000,COLUMN()<=14)- Select your format.
That rule will apply to A2:N6000 even if someone adds/deletes rows/columns.
It basically says: Choose a larger range (e.g. the whole spreadsheet or a set of columns or rows) and limit the range to which the formula is applied inside the formula.
In my case, the spreadsheet C1 contains the number of the first row and C2 contains the number of the last row of the range, to which I want to apply the conditional formatting. The column of the conditional formatting is 'F'. The goal is to mark empty cells in column 'F' red, if the corresponding cell in column 'B' has a value.
The formula is
=AND(ISBLANK($F1); NOT(ISBLANK($B1)); ROW()>=$C$1; ROW()<=$C$2)
Original formula in German:
=UND(ISTLEER($F1); NICHT(ISTLEER($B1)); ZEILE()>=$C$1; ZEILE()<=$C$2)
The "Applies To" range is =$F:$F
Explanation:
"AND(ISBLANK($F1); NOT(ISBLANK($B1)))" is the actual condition.
"ROW()>=$C$1; ROW()<=$C$2" is the additional range limitation to the "Applies To".
Adjust this range limitation to your needs.
Tobias Knauss
- 411
- 2
- 11
1
One workaround is to make your data a dynamic named range. This will allow you to apply the conditional formatting to the named range by name, which will remain constant, while the range the name applies to may change as you add or remove data.
How to set up a dynamic named range:
Go to the Formula ribbon and click Name Manager. In the Name Manager, click New... to create your named range. Give it a descriptive name (no spaces) like MyData. In the Refers to field you can use a formula to define the named range. Something like
=INDIRECT("Sheet1!$A$1:$Q$"&COUNTA(Sheet1!$A:$A))
will refer to all data in A:Q assuming there are no blanks in your data in column A. Now you can use the name MyData to refer to all your data, even if you add or delete rows.
All that's left to do is to redefine your conditional formatting rule with MyData in the Applies to field.
Excellll
- 12,847
0
Microsoft needs to add this feature which means referencing Table name in conditional formatting.
It's something like below.
Applies to: =Table1[Column3]
So many people requesting this feature since 2015 but it's not being implemented yet.
Please vote for this feature.
wonsuc
- 101
-2
I've run into this before. This is very similar to the question Excel conditional formatting fragmentation.
I am pretty sure you can use the same solution, only enter =$A:$Q as the range in the Applies to field.
