0

I have a VBA macro that creates a new sheet and provides a small todo list. One part of it is that you can mark input as done, which then uses Conditional Formatting to adjust the font properties. So far everything works.

Now this macro should also work on English and German laptops. So depending on the language it should insert a different formula.

   'DropDown List
    With Range("F4:F50").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="Yes,No"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

    Dim f As String
    For i = 5 To 50
    f = "F" & i
    Dim frm As String

    frm = "=IF(" & "$F" & "$" & i & "=""YES"",TRUE,FALSE)"

    Range("C" & i & ":" & "E" & i).Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:=frm
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Font
            .Strikethrough = True
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = -0.249946592608417
        End With
    Next i
    Worksheets(sn).Move After:=Sheets(ActiveWorkbook.Sheets.Count)

For German the formular would be frm = "=WENN(" & "$F" & "$" & i & "=""Ja"";WAHR;FALSCH)"

Thanks a lot!

PS: Can be solved without language location. See comments below.

wit4r7
  • 125
  • 1
  • 13
  • Possible duplicate of [VBA for Excel 2010/2013 - How to identify the system locale](https://stackoverflow.com/questions/15500552/vba-for-excel-2010-2013-how-to-identify-the-system-locale) – Dominique Mar 04 '19 at 16:03
  • 1
    Just change your formula; frm = "=$F$" & i & "=""YES""" And no need for Selects as you did in the first part. – EvR Mar 04 '19 at 16:23
  • Thanks a lot for this response. Works perfectly. – wit4r7 Mar 05 '19 at 07:55

0 Answers0