Conditional formatting - Apply to named ranges
Currently if you apply conditional formatting to a named range (i.e.. a range named via the Name Manager or a table style named-range), the value in the "Applies to" column of the Condidtional Formatting Rules Manger will revert the named range to its absolute cell reference.
eg. "Applies to: =Table1[Column3]" becomes "Applies to: =$C$2:$C$7".
Apart from being more difficult to read, it creates other complexities. e.g. if I happen to copy/paste a cell into this range, it creates a mess in the rules manager, creating a new rule for each cell pasted.
e.g. if my named range is 'Table1[Column3]' which corresponds to cells $C$2:$C$7, and I copy say cell C2 and paste it into C8, then in the rules manager another line is for some reason added with the same rule. So now I have one rule for range '$C$2:$C$7' and another one for '$C$9'. Why?!
Having one line for the named range would make things so much more simple.
Graham
shared this idea
Thanks for supporting this feature. We won’t be able to in the near future, but we understand that it would be a nice improvement. Even though it seems like a straightforward idea, it is quite complex since named ranges can be dynamic, meaning that the range is calculated when the workbook is calculated.
We’ll continue tracking votes for this suggestion.
258 comments
-
anomalous
commented
Here is a bunch of workaround scenarios for this - hope it's useful to someone
-
Roger
commented
Second request:
On Aug 29, 2019 Lawrence Dunn said "I've attached a text file containing a thrifted version of my VB module".
But, there is NO attached file to that entry. Where do I find it? -
anomalous
commented
This (very simple) VBA User Defined Formula can be used to avoid all that row() and column() checking...
Function NW_IsWithinRange(CellReference As Range, RangeReference As Range) As Boolean
If CellReference.Row >= RangeReference.Row And _
CellReference.Row <= RangeReference.Row + RangeReference.Rows.Count - 1 And _
CellReference.Column >= RangeReference.Column And _
CellReference.Column <= RangeReference.Column + RangeReference.Columns.Count - 1 Then
NW_IsWithinRange = True
Else: NW_IsWithinRange = False
End If
End Function -
anomalous
commented
I have a solution to this - you won't like it but it works, it doesnt need VBA. I've used it for years without any problems, it doesnt noticably increase the file size or impact calculation times (insofar as I have noticed). My preferrewd solution would be for MS to enable =indirect() or Named Ranges in the range selection. But my approach does work reliably.
Apologies if someone already posted some variant of this solution, I havent read through all the comments (there's just too many of them).
So the solution is to shift the definition of the range from the *range definition* to the formula logic.
Firstly using a blank spreadsheet, create a conditional format set the range to cover $A:$XFD - ie every cell on the worksheet - then using a formula to determine which cells to format, maybe use this horror - you'll need a blunt instrument to crack this not ;-)
=AND(ROW()>7,ROW()<10,COLUMN()>4,COLUMN()<10,NOT(ISBLANK(A1)))
and set the format to be something obvious - perhaps a red background
enter a bunch of random text copy stuff anywhere and I guarantee you wont have any duplicated Conditional Formatting.The other way is to (also apply your conditional format to every cell in the entire worksheet, but use a separate sheet as a *map* of where the Conditional formatting should apply. I used a few other variations on these methods - but they all amount to the same thing - apply your conditional format to ever cell in the sheet and have overly complex formulae - I attached a simple example worksheet with 2 examples show - you'll work out the rest...
Cheers - NigelW
-
zojos
commented
Using named ranges is one (good) possible solution to this problem.... but a solution there must be. It is just intolerable that a adding rows within a range that has CF should cause such chaos in the CF rules.
-
Lawrence Dunn
commented
@Roger, @Andrew Ashurst & @Futski
I had not seen Celia Alves solution previously (Thanks Futski for the link). I've now watched her youtube presentation of the solution.
The solution that Celia is using is not too different to my own.
So Celia is setting up a CF 'master' in one sheet/tab that the user does not touch so it has no risk of being corrupted, & then copy's that master CF into a range on a separate sheet that the user is using & manipulating.
My approach was to write the CF rules directly into a macro, & run the macro to clear the CF & reapply when the rules become corrupted.
Both approaches use macro (VBA) to replace a corrupted CF. The difference is whether you prefer to create your CF in the normal CF tool, or create them directly in the VBA. Both approaches have their pros & cons.
For me, I'm reasonably comfortable writing VBA, so it made sense for me to write my CF rules in VBA directly. If someone is not so comfortable with VBA, then they may be happier using the normal tool to create the CF rules - but they still need to be comfy enough with VBA to create the code that is going to copy the master CF range to the users data range.....both solutions require a bit of VBA knowledge.
One advantage of writing the CF rules directly into VBA, is that it makes it pretty quick & easy to copy & paste a chunk of VBA copy & modify one of the parameters. For me, this is quicker & easier than using the normal CF tool once you already have the basics of the rule in the VBA code to copy/paste.....but for others, they may prefer continuing to use the CF tool to create their CF rules.
-
simao
commented
@Andrew Ashurst
There is alrady one for tables, few votes
https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/38601631-conditional-formatting-tables -
Futski
commented
@Andrew Ashurst, regarding the post from the "Portuguese/Brazilian" lady, I think you were referring to the following post from Celia Alves on Oct 22, 2019. It is definitely an interesting solution that I had not thought of before.
=======================
Celia Alves commented · October 22, 2019 5:29 AM · Flag as inappropriate
I also use VBA to solve this issue. I usually make the macro run on Save so that the next user finds the file properly set up.This video explains how I do it. If you watch it, please read the video description that includes important updates.
https://www.youtube.com/watch?v=pC98JKrfC3g
======================= -
Andrew Ashurst
commented
oh, I looked in the previous admin posts from ms, apparently "TABLES" are structured tables and are included, at least according to Steve of MS 5 years ago, in this proposition. Who knows? Another 5 years to maybe find out I suppose.
-
Andrew Ashurst
commented
As ms can be somewhat lawyerly in its interpretations of these requests perhaps we should start a new proposition "Conditional formatting - Apply to TABLES". If it doesn't already exist (no time to look now). I've found before with other issues that the vote is being split in effect as there are several threads really asking for the same thing but in slightly different ways.
I preferred another solution to Laurence's (his and it are back there in the posts somewhere). His looked complex and hard to fathom how it was doing what it was doing to me. I'm sure it works though. The other solution was a simpler approach but worked well enough for me (posted by a lady with a Portuguese/Brazilian name I think).
-
Roger
commented
Laurence Dunn, on Sept 10, said he attached a "blank Excel workbook that contains the VBA code"... Where is this file - how do I find it?
-
Andrew Ashurst
commented
There are a couple of macro/vba partial solutions posted by users in the previous comments here. One of them worked ok for me - at least it was a lot easier than trying to fix cf manually abeit not as easy as one day we hope it will be when ms fix this.
Aside from that I'd like to echo Joe's comment a few days ago. I think this thread started before Excel introduced "TABLES" (it would have been useful if MS had given them a more differentiating name so that those who don't use "TABLES" might realise that those who do are talking about something specific in Excel and not just any ordinary tabular arrangement of cells that haven't been defined by the creator of the workbook as a "TABLE" (perhaps a "mesa" or a "plano"). Anyway, the point is I think this conversation started when there were just ranges and named_ranges. But, now there are "TABLES" and it is there that I agree with Joe that MS should concentrate it's efforts to fix or improve cf.
-
Brenda
commented
OMG! This is exactly the issue I am having. I have a large data sheet and now I have multiple rules that were automatically created. Its taking forever to delete the extra ones and fix the original ones. Now this is an added step for me evry time I work on this spreadsheet to avoid accumulating extra rules.
-
Anonymous
commented
yes please
-
Joe
commented
> since named ranges can be dynamic, meaning that the range is calculated when the workbook is calculated.
Maybe start with reference to tables? I think it would be enough for majority of the users.
-
R Lien
commented
I want to flag for blank cells in a table to encourage users to complete the table... conditional formatting? Not if I add additional rows or columns to the table.
I'd hoped this was fixed for years... guess not. :( -
Lawrence Dunn
commented
@Steven Soeder - well said!
I'd also like to add, that for each one of use here, who are probably the more advanced Excel users in our respective organisations, there are probably 100+ others in our organisations that have been struggling with Conditional Formatting's range fragmentation habit for years.
The folk posting & voting here are merely the very tip of the Iceberg.
I'm a Senior Engineer at Ford Motor Company & am only around 18 months away from having 3 decades of experience of nigh on daily Excel use. Conditional Formatting is one of those tools thats relatively easy for many users to initially setup & it provides a convenient means to highlight certain cells/rows/columns/ranges etc, but the average user will return to amend, or add new rules after a few weeks & can find the simple CF rules they set an absolute mess & slow & hard to rectify - I suspect most probably just give up.
-
Futski
commented
@Steven Soeder - I think the problem is, as I've seen stated many times before, that MS doesn't actually have anybody that "uses" Excel. And certainly not anyone that could be considered a "power user" like the majority of people in this forum.
-
Steven Soeder
commented
@Excel Team [MSFT] - original response was to vote this up and "we'll prioritize accordingly." Well, we did vote it up, for 5 years, and apparently thats not enough. Just reading through the comments (which we've received continuous alerts on for 5 years now) it seems this is important to many users, so could you please explain why it has been de-priortized? I'm sure your software engineers and coders are pretty clever and could fix this if you asked them to. It's not a matter of it being too hard, it is a matter of it not being viewed as important. I'd like to know why it's not viewed as important, and what you need to see from us to convince you that it is.
-
Anonymous
commented
So between 2015 until 2020 nobody in this planet managed to solve this... Please hire the Russian who developed the Coronavirus's vaccine
