List separator in conditional formatting

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.

=if(1>0,1,0)

And in case of semicolon, every comma will be converted into semicolon.

=if(1>0;1;0)

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.

Debug.Print Application.International(xlListSeparator)

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

Whole code

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? 🙂