FormatConditions Borders not supported-CodePudding

I have some code that should formating cells with a border around (FormatConditions), but I get an error that the object isn´t supported

"VBA Object Doesn’t Support this Property or Method Error (Error 438)"

Can anybody help? I think it depends on MeinBereich.FormatConditions.Borders(xlEdgeLeft)

Private Sub Workbook_Open()     
    Dim my_month As String
    my_month = Format(Date, "mmmm")   " "   Format(Date, "yy")
    Sheets(my_month).Activate
    Range("A1").Select
     
    LR = Cells(Rows.Count, 1).End(xlUp).Row
    
    Dim DatumColumn As Range
    Dim foundRng As Range
    
    my_day = Format(Date, "dd")   "."
    Set foundRng = Range("B2:AF2").Find(my_day)
    
    Dim MeinBereich As Range
    Set MeinBereich = Range(Cells(foundRng.Row, foundRng.Column), Cells(LR, foundRng.Column))
    MeinBereich.FormatConditions.Delete
    
    
    Dim intErgebnis As Integer 
    intErgebnis = StrComp(foundRng, my_day, vbTextCompare)
    
    If intErgebnis = 1 Then
        With MeinBereich
            .FormatConditions.Add Type:=xlExpression, Formula1:="=100"
            With MeinBereich.FormatConditions.Borders(xlEdgeLeft)
                .LineStyle = xlContinuous
                .Color = -16776961
                .TintAndShade = 0
                .Weight = xlThick
            End With
            With MeinBereich.FormatConditions.Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .Color = -16776961
                .TintAndShade = 0
                .Weight = xlThick
            End With
            With MeinBereich.FormatConditions.Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .Color = -16776961
                .TintAndShade = 0
                .Weight = xlThick
            End With
            With MeinBereich.FormatConditions.Borders(xlEdgeRight)
                .LineStyle = xlContinuous
                .Color = -16776961
                .TintAndShade = 0
                .Weight = xlThick
            End With
        End With
    End If
End Sub

CodePudding user response：

Format conditions and borders are a bit tricky - within VBA and GUI as well:

The basic concept:

Dim fc As FormatCondition
With MeinBereich
    .FormatConditions.Delete
    Set fc = .FormatConditions.Add(Type:=xlExpression, Formula1:="=100")
    With fc.Borders(xlBottom)
        .Color = -16776961
    End With
End With

Assign the added format condition to a variable. Then you work with this to set the formatting.

You only have Borders xlBottom, xlTop, xlLeft, xlRight.

You can't set/don't need to set line style nor weight. It is always coninous and light.

As said: that's the same within GUI. If you want a bold line via format condition, you have to go the "reverse" way. Format the whole range with a normal bold border - and set it to "no border" based on the contrary condition (e.g. <> 100).