[SOLVED] VBA Conditional Formatting of specific Borders

VBA Conditional Formatting of specific Borders

Hej, So I am trying to use VBA to add conditonal formatting to compensate for some other coding, which changes the ranges from time to time. My problem is, that I only the conditional formatting to apply to xlEdgeRight and xlEdgeLeft. However, VBA always tells me that it cannot set the border style. Any ideas?

Dim rngMark As Range
Dim DateCond As FormatCondition
Dim BordNum As Long

Call wsDef
Set rngMark = wksS.Range("E11:CPB25")

rngMark.FormatConditions.Delete
Set DateCond = rngMark.FormatConditions.Add(Type:=xlExpression, Formula1:="=AND(TODAY()>=E$7,TODAY()<F$7)")
DateCond.SetFirstPriority
With DateCond
    .StopIfTrue = False
    .Font.ThemeColor = xlThemeColorAccent2
    .Font.Bold = True
    .Borders.LineStyle = xlNone
    .Borders.LineStyle = xlNone
End With
For BordNum = 7 To 8
    With DateCond.Borders(BordNum)
        .LineStyle = xlContinuous
        .TintAndShade = 0
        .Color = -16776961
        .Weight = xlThin
    End With
Next BordNum

Solution

Programming formatconditions - especially borders - is a bit special.

You have to use xlLeft = -4131 and xlRight = -4152 in this case:

Dim BordNum As Long
Dim arrBordNum(1) As Long
arrBordNum(0) = -4131   'xlleft
arrBordNum(1) = -4152   'xlright
For BordNum = 0 To UBound(arrBordNum)
    With DateCond.Borders(arrBordNum(BordNum))
        .LineStyle = xlContinuous
        .TintAndShade = 0
        .Color = -16776961
        .Weight = xlThin
    End With
Next BordNum