1

I have a small VBA code to create Conditional Formatting in Excel, It works well on Excel 365, but it generates "Run-time error 5" when trying in earlier versions of excel (eg. 2016). Could you please advise a fix to make it works on most versions ?

Public Sub CF()

    With Range("K20:ZH20").FormatConditions.Add(Type:=xlExpression, Formula1:="=ISNUMBER(SEARCH(" & Chr(34) & "Project Exec" & Chr(34) & ";$A$20))")
        .Interior.Pattern = xlPatternLightUp
    End With

End Sub

Thanks!

After John Coleman comment, I found that it was about regional setting. It raise error because I tested on computer with difference setting. (the code showing above works for computer with "." as thousand separator and not work for computer use "," as thousand separator. So the question now is how to make the code independent to regional settings ?

K-Laboratory
  • 99
  • 8
  • I don't see any reasons why that should work in a version, but not another (thinking at anything 2010 and up). Try the standalone formula in a cell, and see if it works. – FAB Jun 19 '19 at 15:55
  • 1
    What about `;` => `,`? VBA doesn't know about regional settings. See [this question](https://stackoverflow.com/q/16584132/4996248) – John Coleman Jun 19 '19 at 16:01
  • John Coleman: you are right, it is about the regional settings. The version 365 I tried use "." as thousand separator and the version 2016 use "," as thousand separator. When I change the code above, from ";" => "," it works. How to make codes independent of regional settings in this case ? – K-Laboratory Jun 19 '19 at 16:09
  • 1
    [This answer](https://stackoverflow.com/a/35724386/4996248) provides some more details. Using a comma should be okay. What the end-user sees should reflect their regional settings. That said, this does seem one area where VBA is confusing. – John Coleman Jun 19 '19 at 16:12

1 Answers1

0

I found a solution from another threat that to use FormulaLocal property. The code above to work with any Regional Settings look like this:

Public Sub CF()
    Dim formula as String 
    formula = "=ISNUMBER(SEARCH(" & Chr(34) & "Project Exec" & Chr(34) & ",$A$20))" 
    Range("A1").Formula = formula     
    formula = Range("A1").FormulaLocal
    With Range("K20:ZH20").FormatConditions.Add(Type:=xlExpression, Formula1:=formula)
        .Interior.Pattern = xlPatternLightUp
    Range("A1").ClearContents
    End With

End Sub
K-Laboratory
  • 99
  • 8