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)
235 comments
-
Michael Hutchinson
commented
@Jody Muelaner. Place this in a worksheet object.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Me.Range("MyRange"),Target) Is Nothing Then
Dim cell_x as range
For each cell_x in TargetIf cell_x.Value="SpecialValue" Then
With Cell_x
.Interior.Color=vbRed
.Font.Color=vbWhite
End WithElse
With Cell_x
.Interior.Color=xlNone
.Font.Color=vbBlack
End WithEnd if
next cell_x
End If
End Sub
If you wanted the code to trigger on any change to the worksheet, you could remove the outer If statement and change the For each to iterate over Me.Range("MyRange"). There are other nuances to Conditional Formatting which you may want to duplicate, but hopefully this gives you a starter for ten.
-
James Goodhew
commented
Definitely need to maintain structured and named range references in "Applies To" box as well please.
-
Jody Muelaner
commented
Michael Hutchinson - could you give an example of the VBA code required to create a robust conditional formating rule please?
-
Michael Hutchinson
commented
You can get around this using Worksheet_Change() and intersect(x, target) style code in VBA...though admittedly not as user friendly.
-
Toni
commented
Please, I would love to format according to conditions, but as it is now it is kind of useless since I can't trust what is does! I said please, so you have to at least give it a look.... Been 5 years already since this was posted and you guys have done nothing!
-
Pat Babcock
commented
Would be useful!
-
Ben Rowland
commented
If nothing else, at least make the Rules Manager more functional than a static sized box with zero bulk functions. If you're not going to give us a proper feature, then at least give us the ability to clean up the resulting mess better.
-
Hảo Lưu Tuệ commented
Excel/Office team, please keep your sheets together and fix old "features" before throwing out new useless updates that break things up even more (looking at you, '@' sign). I voted a while back and keep receiving comment notification mails every other week from desperate users like me complaining about their ranges turning into fragmentation bombs.
-
Jim White
commented
The 'spam explosion' of CF rules that you get whenever users copy/paste/etc. is ridiculous. I've upvoted, although given this has been open since 2015, and is the top-rate suggestion within 'formatting', it suggests that MS are not really interested in listening to any of our votes.
-
Geoffroy Menard
commented
It should also allow to use namedranges in the test formula.
-
Malcolm
commented
I'm also finding that the most common reason Excel crashes and needs to repair a file is to do with conditional formats in tables. When "repaired" the conditional formats have been stripped out entirely. It only occurs in tables when there is a formula used to select the conditional format but it occurs so regularly for me that I now have macros in my spreadsheets that detect if a conditional format has "disappeared" and re-instates it!
When looking at dealing with the named ranged issue, please can you also look to remove whatever bug causes these table-based conditional formats to crash Excel?
-
Bleddyn James
commented
Has anyone been able to contact a Program Manager from the Excel team?
I tried reaching out to someone in the Excel team via LinkedIn.If anyone know's of anyone in the Excel team at Microsoft may be worth trying another avenue and linking them to this post?
I agree it's ridiculous that this suggestion has more than 850+ votes, has been open since 2015 and no action has been taken by the Excel Team.
I just posted on the Excel Product Teams FY20 Goals Blog:
https://techcommunity.microsoft.com/t5/excel-blog/excel-product-team-fy20-goals/bc-p/1448180#M1388Hopefully this is still the correct channel for raising product suggestions....
-
Anonymous
commented
This post started in 2015, and remains a problem 5+ years later. Its such an obvious hole in the way conditional formatting works. Clearly MS is ignoring us.
-
Peter Collins
commented
@NC the problem is that excel converts named ranges into references that aren't dynamic. So if the size of the namerange changes, the CF doesn't update.
-
NC
commented
Clarify previous comment: when I say "should be possible" I mean that this is already supported, you should be able to do it without difficultiy. I have named ranges working nicely in CF formulae.
-
NC
commented
The field size for editing the formula of conditional formats is way too small. It should be possible to create complex formatting formula that use named ranges.
-
Marco
commented
Please do this
-
Geoffroy Menard
commented
We'd also need to be able to use Named Ranges in conditional formatting formulas
-
Bleddyn James
commented
@Excel Team [MSFT]
Why bother have a feedback suggestions area if you aren't going to action the feedback.
From what I can see this is the 11th most voted item to date.Almost 5 years on there are still users requesting this functionality and yet still no action to introduce this functionality.
Extremely disappointing.
-
Peter Collins
commented
Can we just not copy conditional formating over to tables. When is it ever needed to copy conditional formating to another table? Conditional formatting should only ever apply to the table it's applied to and not whatever table data is being pasted to. Copying conditional formating should be done from the conditional formating pane itself and into the conditional formating pane, i.e. I can copy paste formating rules.
Stopping conditional formatting from being copied from cells would be a huge improvement in of itself.