Recorded macro for conditional formatting error: "Unable to set the LineStyle property of the Border class"

Question

I have a worksheet where there are two conditional formats applied to columns U and V respectively.

I wish to apply a new conditional format to the whole relevant data range, and this include what is in columns U and V.

I recorded the following, which worked while recording.

Sub Macro13()
'
' Macro13 Macro
'

'
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$C1<>$C2"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Borders(xlBottom)
        .LineStyle = xlContinuous
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

The macro is exactly as Excel produced it.

Running the macro produces an error.

"Unable to set the LineStyle property of the Border class"

occurs in

.LineStyle = xlContinuous

Why does this work when recording it but not when run? How can I change it so that it works correctly?

I am using Excel 2007 on a Windows 7 Professional computer.

I searched the site and one question was asked about this. The problem was not addressed. Instead, workarounds were offered that avoided the problem, rather than explaining why the code would not work.

UPDATE
If I insert the line Selection.FormatConditions.Delete, like so:

Sub Macro13()
'
' Macro13 Macro
'

'
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select

    Selection.FormatConditions.Delete  ' <-----------Added here

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$C1<>$C2"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Borders(xlBottom)
       .LineStyle = xlContinuous
       .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

the code produces the underlining I want, but it also removes the previous conditional formatting of columns U and V, making the change useless.

Answer 1

See commented code below for explanation. Also see this link on how to avoid using Select - something the macro recorder is notorious for!

Sub AddFormatting()

    ' Create a range object so Select isn't needed, use the same xlToRight and xlDown methods though

    Dim myRange As Range

    With ActiveSheet
        Set myRange = .Range(.Range("A1"), .Range("A1").End(xlToRight).End(xlDown))
    End With

    ' Note you may not want to use the above method, as it formats the whole document if there is nothing in row 1!!
    ' You could remove the above With block and consider using instead:
    ' Set myRange = ActivesSheet.UsedRange

    ' Add a new Format Condition
    myRange.FormatConditions.Add Type:=xlExpression, Formula1:="=$C1<>$C2"

    ' Make it the first Format Condition (Macro recorder's default)
    ' Note you may not want this priority order in your conditional formats!
    ' For now though, it makes referencing the format simpler as it's now FormatConditions(1)

    myRange.FormatConditions(myRange.FormatConditions.Count).SetFirstPriority

    With myRange.FormatConditions(1).Borders(xlBottom)
        .LineStyle = xlContinuous
        .TintAndShade = 0
        .Weight = xlThin
    End With

    ' Again, this is excel's default but may not be what you want, look up StopIfTrue
    myRange.FormatConditions(1).StopIfTrue = False

End Sub

A final note:

You may actually want to delete all previous conditional formatting in your sheet at the start of this sub, and recreate them in the same way in VBA. This is because interacting with cells in Excel often splits up and complicates the conditional formats - creating a document which slowly grinds to a halt! It would also ensure you don't double-add the conditional format which is actually created above.

Hope this helps.