Vba apply conditional formatting top/bottom border only?

0
votes

I am using the following vba code to apply conditional formatting.

Sub ResetConditions()
    With Worksheets(1).Range("A9:P1048576")
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=ROW(B9)=ROW(OFFSET($B$9,COUNTA($B:$B)-2,0))"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority

            With .Borders
            .LineStyle = xlContinuous
            .Weight = xlThin
            .Color = vbRed
            End With

        End With
    End With
End Sub

The border is showing as this:

enter image description here

But i want it to look like this:

enter image description here

I am trying to set only top/bottom borders like so:

Sub ResetConditions()
        With Worksheets(1).Range("A9:P1048576")
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
              "=ROW(B9)=ROW(OFFSET($B$9,COUNTA($B:$B)-2,0))"
            With .FormatConditions(.FormatConditions.Count)
                .SetFirstPriority

                With .Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .Color = vbRed
                End With

            End With
        End With
    End Sub

But i keep getting an error unable to set linestyle property of border class.

Please can someone show me where i am going wrong?

3
excelvbaconditional-formatting
Try recording a macro while setting conditional formatting using top border format. You will see, that Excel itself will using .Borders(xlTop) instead of .Borders(xlEdgeTop). So probably Borders Object for ranges differs from Borders collection from FormatCondition.Borders in this not documented way. – Axel Richter

3 Answers

1
votes

Try it like this...

Sub ResetConditions()
    Dim ws As Worksheet
    Dim Rng As Range
    Dim n As Integer
    Set ws = Sheets(1)
    Set Rng = ws.Range("A9:P1048576")

    Rng.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=ROW(B9)=ROW(OFFSET($B$9,COUNTA($B:$B)-2,0))"
    n = Rng.FormatConditions.Count
    Rng.FormatConditions(n).SetFirstPriority
    With Rng.FormatConditions(n).Borders(xlTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .Color = vbRed
    End With
    With Rng.FormatConditions(n).Borders(xlBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .Color = vbRed
    End With
End Sub
0
votes

This is what I use for border ranges:

Public Sub BorderMe(my_range)

    Dim l_counter   As Long

    For l_counter = 7 To 10 '7 to 10 are the magic numbers for xlEdgeLeft etc
        With my_range.Borders(l_counter)
            .LineStyle = xlContinuous
            .Weight = xlMedium
        End With
    Next l_counter

End Sub

You may edit the color, the weight, style and etc. The magic ist that 7,8,9 and 10 are the numbers of Excel for xlEdgeLeft, xlEdgeRight, xlEdgeTop and xlEdgeBottom.

Run it like this:call borderme(selection) in the immediate window, to see what is does.

0
votes
   Rng.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
    Formula1:="=10"
Rng.FormatConditions(Rng.FormatConditions.Count).SetFirstPriority
With Rng.FormatConditions(Rng.FormatConditions.Count).Borders(xlTop)
    .LineStyle = xlContinuous
    .TintAndShade = 0
    .Weight = xlThin
    .Color = vbRed
End With
With Rng.FormatConditions(Rng.FormatConditions.Count).Borders(xlBottom)
    .LineStyle = xlContinuous
    .TintAndShade = 0
    .Weight = xlThin
    .Color = vbRed
End With

Try this code dont forget to set rng Set Rng = Range("")