Have You ever thought about list separator and its connection with conditional formatting before? If not, hop in, read and You’ll be surprised what I found out about it!
I have never thought that the list separator in formula can cause me any issue, until I started to work in English system version. It occurs, that by changing language, it changes also your regional settings (in most cases).
What do You mainly notice after that change?
Your decimal and list separator! In today’s article I’m going to focus on the second one.
Why is that an issue?
You may ask:
Hey man, what is the problem? In VBA editor there is only 1 default language – english and 1 list separator – comma. What are You talking about?
You are right, but… I am too.
Let me show it on example. To fill Excel cell with formula You can use something like this below.
Cells(1, 1).Value = "=if(1>0,1,0)"
In this case, whatever your system list separator is, first cell on your Excel sheet fills with this formula. It will be correct, because Excel will convert that comma (default list separator of VBA) into its list separator.
So in case of comma this formula looks in cell the same as in quotes.
And in case of semicolon, every comma will be converted into semicolon.
Everything will be correct and working.
So where is the problem?
It turns out, that Excel behaves differently in case of conditional formatting formulas. To present the situation better, I wrote simple example.
Option Explicit Sub listSeparator() With Cells(1, 1) .FormatConditions.Add Type:=xlExpression, Formula1:="=IF(1>0,1,0)" .FormatConditions(.FormatConditions.Count).SetFirstPriority With .FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 255 .TintAndShade = 0 End With .FormatConditions(1).StopIfTrue = False End With End Sub
If You are using VBA to set that kind of formatting, You must be consistent with your regional settings in Formula1. In other case You will get the error.
I can’t really say why is this happening, but I knew that after several changes of regional settings I had enough and decided to end it. I found out that You can actually check what is the list separator using VBA.
Thanks to that macro will know what is the list separator. Now We have two approaches: write conditional function with 2 variants of formula or write universal formula with list separator as string variable.
Dim uniFormula As String Dim listSep As String listSep = Application.International(xlListSeparator) '#1 variant uniFormula = "=IF(1>0" & listSep & "1" & listSep & "0)" '#2 variant If listSep = "," Then uniFormula = "=IF(1>0,1,0)" Else uniFormula = "=IF(1>0;1;0)" End If
Just put code from above in the beginning of Sub listSeparator() and place universal formula uniFormula after Formula1:=.
Option Explicit Sub listSeparator() Cells(1, 1).Value = "=if(1>0,1,0)" Dim uniFormula As String Dim listSep As String listSep = Application.International(xlListSeparator) '#1 uniFormula = "=IF(1>0" & listSep & "1" & listSep & "0)" '#2 If listSep = "," Then uniFormula = "=IF(1>0,1,0)" Else uniFormula = "=IF(1>0;1;0)" End If With Cells(1, 1) .FormatConditions.Add Type:=xlExpression, Formula1:=uniFormula .FormatConditions(.FormatConditions.Count).SetFirstPriority With .FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 255 .TintAndShade = 0 End With .FormatConditions(1).StopIfTrue = False End With End Sub
To sum up…
Now I have no issue with list separator in conditional formatting formulas. As it was in one of the memes, I don’t always use conditional formatting in Visual Basic, but when I do, I always check list separator.
Do You know which meme is it? 🙂
7 thoughts on “List separator in conditional formatting”
Just so you know, it is much worse than this, because if you have your Excel application language in a different to English language, the Formula1 has to be entirely in, say, German, not just the list separator.
Since I write VB.NET running Excel on about 9 front-end languages and 40 regional settings, I’ve written a routine that converts a formula from “English” format (commas and English commands) to local format using the .Formula / .FormulaLocal and .FormulaR1C1 / .FormulaR1C1Local properties to transform programming in English to the relevant Excel UI language and Regional Settings format before putting the localised version into .Formula1
You have to do the same with Data Validation and formulae in Named Ranges.
Recently I realised that if You create a tool for different “languages”, it is needed to use that “transformation”.
Thank You for your input, really valuable!
Hi Neil, I’m running into the same issue and was also thinking about creating a wrapper sub to “translate” the formulas I need in my conditional formatting.
Can you share some hints how you tackled it?
For example, how do you get hold of the language Excel uses for the formulas?
Are you using the REPLACE formula to replace the function keywords or do you take a totally different (perhaps more clever and failsafe) approach on it?
Thanks for the list separator fix Tomasz. That saved me a lot of headaches 😉