Conditional Formatting with VBA: Border at Every Change in Value in a Given Column

[lneidorf]

Hi there.

I'm using Excel for Mac 16.53, with an Office 365 subscription.

I'd like to create a macro for repeated use to apply borders to my spreadsheets whenever there's a change in value in column B. But I want to do this via Conditional Formatting, as the values in the spreadsheets can change over time.

I've got some code that's erroring out. Here's my code:

Sub FamilyBanding()
'Define Range
Dim MyRange As Range
Set MyRange = Selection
 
'Delete Existing Conditional Formatting from Range
MyRange.FormatConditions.Delete
 
'Apply Conditional Formatting
MyRange.FormatConditions.Add Type:=xlExpression, _
        Formula1:="=$B3<>$B2"
    With MyRange.FormatConditions(1).Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ThemeColor = 5
        .TintAndShade = 0
        .Weight = xlThin
    End With
End Sub

When I run this, I get a Runtime Error 1004: Unable to set the LineStyle property of the Border Class. It seems to be stuck on the ".LineStyle = xlContinuous" line. I should note that it does successfully create the formatting rule, with the proper formula and applied range.

I'd be most grateful for your help.

Thanks!

 

[lneidorf]

Problem solved.

Apparently it doesn't like the "Borders(xlEdgeBottom)" property. When I change this to "Borders(xlBottom)", it runs.