I’m trying to use VBA to apply conditional formatting to some cells in Excel. I want to change the font color, interior cell fill color, and also the borders based on a formula condition.

I’ve successfully managed to change the interior cell color and font color, but I keep running into a Run-time error 1004: Unable to set the LineStyle property of the Border class when trying to apply border formatting.

Here’s the code snippet I’ve tried:

Sub FormatCellsWithConditionalFormatting()
    ' Define your variables and objects
    Dim cell As Range
    Dim part1 As String, part2 As String, formula As String
    Dim borderColour As Long, textSize As Integer

    ' Example values for the variables
    borderColour = RGB(200, 200, 200)
    textSize = 10

    ' Assuming you have your cell object defined somewhere
    Set cell = ThisWorkbook.Sheets("Sheet1").Range("A1") ' Example range

    ' Define the parts of the formula
    part1 = InputSheet.Range("E41").Value
    part2 = InputSheet.Range("M6").Value
    formula = "='Sheet2'!" & part1 & " >= " & part2
    
    ' Clear any existing conditional formatting
    cell.FormatConditions.Delete

    ' Add new conditional formatting
    With cell.FormatConditions.Add(Type:=xlExpression, Formula1:=formula)
        .Interior.Color = borderColour
        .Font.Color = GetOptimalFontColor(borderColour)
        
        ' Attempting to apply border formatting conditionally
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .Color = RGB(0, 0, 0)
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .Color = RGB(0, 0, 0)
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Color = RGB(0, 0, 0)
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .Color = RGB(0, 0, 0)
            .TintAndShade = 0
            .Weight = xlThin
        End With
    End With
End Sub

Function GetOptimalFontColor(bgColor As Long) As Long
    ' Example function to return either black or white font color based on background color
    Dim red As Integer, green As Integer, blue As Integer
    red = bgColor Mod 256
    green = (bgColor  256) Mod 256
    blue = (bgColor  65536) Mod 256
    
    If (red * 0.299 + green * 0.587 + blue * 0.114) > 186 Then
        GetOptimalFontColor = RGB(0, 0, 0) ' Dark colors - use black font
    Else
        GetOptimalFontColor = RGB(255, 255, 255) ' Light colors - use white font
    End If
End Function

Can anyone please tell me, is it possible to conditionally format the font color, interior cell fill color, and also the borders based on a formula condition? If so, can you also show me example code to do this? Thank you!