CODEWITHSUNDEEP
excelvba
Tobias
Tobias

Reputation: 5443

Excel VBA - FormatConditions

i want to use the follow vba code in my application

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

I'm getting an error

Error Number 5. Invalid Argument

What is wrong with that code? THX

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

    ' 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$5=$F$1"
    .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

Upvotes: 3

Views: 7234

Answers (2)

Random Tourist
Random Tourist

Reputation: 191

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

Use 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.

Upvotes: 1

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

The reason are regional settings on your computer. Instead of comma you should use semicolon to separate formula arguments. Therefore instead of this line:

.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.

Upvotes: 6

Related Questions