Excel VBA - 格式条件

Excel VBA - FormatConditions

提问by Tobias

i want to use the follow vba code in my application

我想在我的应用程序中使用以下 vba 代码

.FormatConditions.Add Type:=xlExpression, Formula1:="=Mod(Column(), 2)"

I'm getting an error

我收到一个错误

Error Number 5. Invalid Argument

错误号 5. 无效参数

What is wrong with that code? THX

那个代码有什么问题？谢谢

This is my complete code in my sub.. all FomatConditions are working fine - except the last two..

这是我的子文件中的完整代码..所有 FomatConditions 都工作正常 - 除了最后两个..

    ' FormatConditions
With Range("K6:BH" & lastUsedRow)
    .FormatConditions.Delete
    ' Prozent
    .FormatConditions.Add Type:=xlExpression, Formula1:="=Prozent"
    .FormatConditions(1).StopIfTrue = False
    .FormatConditions(1).Interior.Pattern = xlNone
    .FormatConditions(1).Interior.Color = RGB(174, 170, 170)
    With .FormatConditions(1).Borders(xlBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 2
        .Weight = xlThin
    End With
    ' Prozent unter
    .FormatConditions.Add Type:=xlExpression, Formula1:="=ProzentUnter"
    .FormatConditions(2).StopIfTrue = False
    .FormatConditions(2).Interior.Color = RGB(255, 192, 0)
    With .FormatConditions(2).Borders(xlBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 2
        .Weight = xlThin
    End With
    ' ist
    .FormatConditions.Add Type:=xlExpression, Formula1:="=Ist"
    .FormatConditions(3).StopIfTrue = False
    .FormatConditions(3).Interior.Color = RGB(208, 206, 206)
    .FormatConditions(3).Interior.Pattern = xlLightUp
    .FormatConditions(3).Interior.PatternColor = RGB(68, 84, 106)
    With .FormatConditions(3).Borders(xlBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 2
        .Weight = xlThin
    End With
    ' ist unter
    .FormatConditions.Add Type:=xlExpression, Formula1:="=IstUnter"
    .FormatConditions(4).StopIfTrue = False
    .FormatConditions(4).Interior.Color = RGB(255, 192, 0)
    .FormatConditions(4).Interior.Pattern = xlLightUp
    .FormatConditions(4).Interior.PatternColor = RGB(68, 84, 106)
    With .FormatConditions(4).Borders(xlBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 2
        .Weight = xlThin
    End With
    ' Plan
    .FormatConditions.Add Type:=xlExpression, Formula1:="=Planen"
    .FormatConditions(5).StopIfTrue = False
    .FormatConditions(5).Interior.Color = RGB(255, 255, 255)
    .FormatConditions(5).Interior.Pattern = xlLightUp
    .FormatConditions(5).Interior.PatternColor = RGB(68, 84, 106)
    With .FormatConditions(5).Borders(xlBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 2
        .Weight = xlThin
    End With
    ' timee
    .FormatConditions.Add Type:=xlExpression, Formula1:="=K=$F"
    .FormatConditions(6).StopIfTrue = False
    .FormatConditions(6).Interior.Color = RGB(198, 224, 180)
    With .FormatConditions(6).Borders(xlLeft)
        .LineStyle = xlContinuous
        .Color = RGB(209, 136, 27)
        .Weight = xlThin
    End With
    With .FormatConditions(6).Borders(xlRight)
        .LineStyle = xlContinuous
        .Color = RGB(209, 136, 27)
        .Weight = xlThin
    End With

    .FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(COLUMN(), 2)"
    .FormatConditions(7).Interior.Color = RGB(242, 242, 242)
    '.FormatConditions.Add Type:=xlExpression, Formula1:="=Mod(Column(), 2)=0"
    '.FormatConditions(8).Interior.Color = RGB(255, 255, 255)
End With

回答by Kazimierz Jawor

The reason are regional settings on your computer. Instead of commayou should use semicolonto separate formula arguments. Therefore instead of this line:

原因是您计算机上的区域设置。而不是comma您应该使用semicolon来分隔公式参数。因此，而不是这一行：

.FormatConditions.Add Type:=xlExpression, Formula1:="=Mod(Column(), 2)"

use this one:

使用这个：

.FormatConditions.Add Type:=xlExpression, Formula1:="=Mod(Column(); 2)"

Or you could change your regional settings alternatively.

或者，您也可以更改区域设置。

回答by Random Tourist

A very good answer has already been posted but as a complement:

已经发布了一个很好的答案，但作为补充：

Use Application.International(xlListSeparator).

使用Application.International(xlListSeparator).

Dim sep As String: sep = Application.International(xlListSeparator)
.FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(COLUMN()" & sep & "2)"

This should work no matter the regional settings.

无论区域设置如何，这都应该有效。