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
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!