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 to Graham for starting this conversation. If you would also like Excel to maintain named range references and structured table references in the “applies to” field for Conditional Formatting rules, please add your comments and vote this one up. We will prioritize accordingly.
Thanks,
Steve (MS Excel)
213 comments
-
Alek Vila
commented
I understand this is your interpretation. It is not what I intended.
By the way, I clarified my intention with "... email clients doing their thing," which came before your comment.
-
Roy
commented
@Alek
No, no confustion. You stated:
"The problem with using VBA or Macros is that the code is stripped out when the file is emailed."
and that was it. Beginning and end of comment. No modifers, no "by organizational policy." Nothing. Just that and only that. The statement, as stated, says it is done by the programs in question, not by someone's choice.
I'm not sh*tt*ng on you for it, just that's what you said and I pointed out it is NOT correct. Leave a thing like that uncorrected and not only will it keep coming up as a set fact (even though it is not), confusing this issue, but some people will act, or rather not act, thinking it is set fact.
No confusion at all.
-
Alek Vila
commented
You might be confusing me with someone else.
-
Roy
commented
@Alek
Sure, sure. Lots of folks cannot use macros by organizational choice. And by theory as well when boss types want to have some ability to delve the spreadsheet to assure themselves it is "correct" and can be trusted. Since few seem to have any VBA skills, they cannot evaluate a macro for this purpose and so the spreadsheet has to be accepted as holy writ.
(You presented it as a feature of Excel and I was addressing the fact that that wasn't so.)
-
Alek Vila
commented
That sound reasonable, but in our case with thousands of clients our department has been instructed not to use Macros or VBA because of the hassle of the various email clients doing their thing.
-
Roy
commented
@Alek
That would be an organizational thing that you might be able to overcome. Or possibly some option set in Outlook or Excel, but I've not heard of one.
It is certainly NOT something that HAS to happen. I can email macro-containing spreadsheets without them being stripped and I am doing nothing special. And that's with working for an organization that definitely makes some perverse "higher level" choices for everyone.
Seriously, a macro to reset the conditional formatting has to do so very, very little actual work that one might just set it to run automatically on every little action taken and one ought never really notice it, ever. Might want to modify that a wee bit to not risk interaction with other macros, but...
(Re-setting 250 rules might task it. But setting them on a range of 300,000 cells is the same work as a range of 4 cells. Though I suppose Excel would re-calculate to make sure it's displaying according to them so... that could be quite a burden. But one could test for the "noticeable point" before dialing it back to On Open/On Save/Button to let the user choose when/etc.)
-
Alek Vila
commented
The problem with using VBA or Macros is that the code is stripped out when the file is emailed.
-
Andrew Ashurst
commented
otimo solucao Celia. Parece pra me imediatement comprehensivel e elegante. Ha outra solucao de vba aqui de Laurence Dunn, e eu tem aquele tambem. Nao tentei usar o solusao dele, mais, so dei um olhado e o codigo parece bem mais complicado do seu. Para ser justo de Laurence tambem o solucao dele pode ser mais sofisticado tambem, mais seu parece suficiente bom por me.
Hope my Portuguese is intelligible :-)
-
Celia Alves
commented
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 -
EPSU
commented
Just wanted to take a moment to thank Lawrence Dunn for sharing his VBA "CF-tidying" code, complete with useful comments. Thank you! @|:~)
-
Christopher Tindall
commented
The only approach I've found to control Conditional Formatting is to prevent user ability to copy and paste rows or cells. This requires VBA buttons to add rows or columns (by inserting a row, copying the info from the row below, then clearing all of the cells for that row). I've used this approach on a 1000+ row spreadsheet with 12+ people simultaneously inserting and deleting rows... not one change to the 10+ conditional formatting formulas.
MS understands this issue is important to the users. This suggestion is ranked #11 in number of votes (an a few higher on the list appear to be solved by the latest update). This board is too valuable for direct user engagement for MS to ignore.
That said, Conditional Formatting is a volatile function without a decent workaround, which makes this difficult to correct without a big update to existing Excel coding. These kind of updates bring risk. Since this is essentially a visual issue, it has less priority than improvements which will increase user efficiency. This is evident in the latest updates which bring very powerful tools (AI, Ideas, New Data Types, Data from Pictures, and Dynamic Arrays). These are added features, not corrections to existing features, so there is less risk.
-
Lawrence Dunn
commented
please take a look at my post a few comments below & the attachment.
I found a 'work around' for me was to write a macro/VB code that contains the conditional format range, rules & formats required. & then run that.
Sure, overtime the Conditional Format gets fragmented & messy, but then you can just re-run the macro to clear all the prior CF & reset it all.
Once you have the VB code, I found it more convenient to just duplicate sections & adjust things in the VB code, than to write new rules in the CF popup window tool.
Take a look & give it a go, its working for me.
-
Futski
commented
@Jan, you are right, there is a lot of shouting at deaf ears. Unfortunately the selectively deaf ears don't want to hear what we're saying. And it's not just this topic.
Is anyone aware of any recent MS response to any topic??? My guess is that MS has completely abandoned this forum. But they leave this out here to make us feel like they are listening. So sad that a perfectly good tool is going to waste.
-
Jan-Rienk Hemminga
commented
@Tony Although you might have a point, I do not think this is a place to raise this issue. This is where we’re trying to get conditional formatting to work with named ranges. This is something that data validation already does. (On a side note, if table names don’t work for data validation, I think you can get it to work by using a name that refers to the table.).
@Microsoft: So what’s the status on this? This has been the number one suggestion in formatting for a couple of years now. And it feels a lot like we’re shouting to deaf ears.
-
Tony Smith
commented
Just want to say this would be super helpful. Not sure if anyone else has mentioned this, but updating the Data Validation dialog to have the same features as conditional formatting after this update would also be HUGE. Data validation is super important but often difficult to manage.
It might also be helpful to have a designation similar to $ such that the range does not split but instead re-applies to everything between the first and last cell. For instance, when inserting a copied row into row 3 that does not have conditional formatting, if the CF applies to box reads #A#1:#A#7, it would update to #A#1:#A#8 instead of A1:A2,A4:A8
-
Lawrence Dunn
commented
Message for those Users receiving updates on the 'feedback to improve Excel Conditional Formatting' thread - like many of you I guess, I periodically get notification when some adds a post to this thread.
After getting fed up with heavily fragmented rules for the umpteenth time on a document that I've been using & evolving for years, I have developed something of a work-around to the issue of fragmenting Conditional Format ('CF' from now on) rules, that I thought I'd share as it might help others here try or adopt an alternative approach to the subject.
Rather than write the rules into the CF tool pop-up box, I've written a macro which starts by clearing ALL CF on my worksheet, & then it creates my CF rules afresh.
While this does not prevent or avoid fragmented rules, what it does mean is that when you need to modify the CF rules, or add new ones, you can quickly, easily & painlessly get back to your original starting point prior to the fragmentation.
I've actually found it more convenient to add new CF rules via copy-pasting sections of code & modifying key pieces of info (like column or row reference, or the formula controlling what receives the CF), rather than creating a new rule in the CF tool pop up.
I've attached a text file containing a thrifted version of my VB module, just cut & paste into a module on your workbook, & modify the key pieces of data like column & row references & the condition formula & formating to suit your needs. I've commented the code.
I hope this helps some of you work around the CF fragmentation issues.
-
Lawrence Dunn
commented
Message for those Users receiving updates on the 'feedback to improve Excel Conditional Formatting' thread - like many of you I guess, I periodically get notification when some adds a post to this thread.
After getting fed up with heavily fragmented rules for the umpteenth time on a document that I've been using & evolving for years, I have developed something of a work-around to the issue of fragmenting Conditional Format ('CF' from now on) rules, that I thought I'd share as it might help others here try or adopt an alternative approach to the subject.
Rather than write the rules into the CF tool pop-up box, I've written a macro which starts by clearing ALL CF on my worksheet, & then it creates my CF rules afresh.
While this does not prevent or avoid fragmented rules, what it does mean is that when you need to modify the CF rules, or add new ones, you can quickly, easily & painlessly get back to your original starting point prior to the fragmentation.
I've actually found it more convenient to add new CF rules via copy-pasting sections of code & modifying key pieces of info (like column or row reference, or the formula controlling what receives the CF), rather than creating a new rule in the CF tool pop up.
I've attached a VB module containing just a thrifted version of my VB Code so others can copy-paste it into a module in their work books & then modify the key pieces of info to tailor to the needs of their workbook.
I hope this helps some of you work around the CF fragmentation issues,
-
Bleddyn James
commented
@Excel Team [MSFT]
How many votes are required for this to be incorporated into the next release of Excel?Managing and maintaining conditional formatting without Named Ranges is an absolute nightmare. Especially if you have to update the formatting.
I currently have thousands of conditional format rules, that would consolidate into one rule to manage if Named Ranges were an option.
Screenshot attached....
-
Chris Newman
commented
This needs to happen!!! Would make life so much easier!
-
Roy
commented
I noticed, in the experimenting for the last post, that as rule never "exists" for the user with no "applies to" defined. It will at least default to the selected cell, set up the rule, and present it fresh and new with at least that selected cell in the "applies to" box.
One cannot get it to take anything not a cell address of some kind. If it does not resolve as a range, it is ignored, life moves on.
But if the value for it could be given, and be pretty unacceptable, BEFORE there is a "previous value" for it to restore... I wonder if they considered this possibility. After all, it's been around long enough to pre-date VBA and Excel might've figured there was no avenue to try to make it happen. Might not have closed off the VBA route.
So if one creates a CF rule with VBA but specifies a NR for the "applies to" data, I wonder if Excel would take it and keep it, presenting it at least one time via the CF tools in the Ribbon. ('Cause it would probably literalize it then, though maybe looking at it and closing without making any change of any kind...)
If so, and if it evaluated it and it really worked as a CF rule, that would do what we want. Creation and changing to a different NR would all have to be done with VBA, not the amazingly obtuse Ribbon tools, but it would be what we want. Not how we want it, but what we want. Especially if changes to the range in the NR were acted upon in the area of effect for the CF rule.
