VBA borders error - exact code by recorder not working

623 views Asked by At

I know the macro recorder generates very inefficient code, so I use it just to get the proper syntax for certain properties I'm not familiar with (borders for example!).

The task I'm trying to accomplish is fairly straightforward -- I'm trying to highlight (and unhighlight) a range based on user input.

However, I'm running into the following problem: the code that the macro recorder uses cannot be duplicated! If I set everything back to how it was before it ran, select the proper range (since the macro recorder makes such extensive use of selection.), and run the macro it throw a compile error: method or data member not found.

The code:

Sub Macro3()
'
' Macro3 Macro
'

'
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ThemeColor = 2
        .TintAndShade = 0.249946592608417
        .Weight = xlHairline
    End With
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ThemeColor = 2
        .TintAndShade = 0.249946592608417
        .Weight = xlHairline
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ThemeColor = 2
        .TintAndShade = 0.249946592608417
        .Weight = xlHairline
    End With
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
End Sub
1

There are 1 answers

0
Austin Wismer On

I guess this isn't a strictly an answer to my question, but rather a solution to my problem.

As mentioned in the last comment, I suspect the error had something to do with the pre-existing borders that were already there. Perhaps the macro simply could not find them in the specific way in which it was recorded.

Instead of using a macro to try to change the borders on the fly (as it's not a simple matter of adding or removing a single border), I've decided to use a different approach:

I will use the regular table formatting (with borders!) as default. Then, instead of using a macro to highlight (make an outside yellow border) around specific groups of cells depending on what is selected, I will simply use conditional formatting connected to a switch. Thus, the VBA will only control the switch, and the built-in conditional formatting will do the rest -- much easier that way!