How to Change Borders in Microsoft Excel VBA

Updated April 17, 2017

The borders in Microsoft Excel help you organise and highlight the information in your workbooks. But as your data changes, your borders might need to change also. Changing them manually can be tedious and prone to error. Programs written in Visual Basic for Applications (VBA) don't have these problems. You might use such a program to keep a border around a particular row in a table. If you apply a border around the row, the row will lose the border when you sort the table. A VBA program can locate the row's new position and reapply the border to it.

Click the "Office" button, and then click the "Options" button. Select the "Show developer" check box, and then click "OK" to make Excel display the tab for recording VBA macros.

Click the "Record" button on the "Developer" tab, and then type "MakeBorder" or a similar name in the text box of the dialogue box that appears. Click "OK" to begin recording the macro for changing borders.

Click any cell in the current spreadsheet, and then click the "Home" tab. Click the down arrow of the "Border" control in the "Font" panel, and then click the "Thick box" item on the list that appears. Excel will paint a thick black border around the cell you clicked.

On the "Developer" tab, click the "Stop recording" button, and then click the "Macros" button on the same tab. Excel will display a list of macros. Click the one you just recorded, and then click the "Edit" button to enter the Visual Basic development environment. Excel will display a list of VBA programming statements that create the thick black border.

Click your mouse to the right of the first "End Width" statement, and then drag down to just before the "End Sub" statement and release the mouse. Press "Delete" to delete the statements. You've deleted all but one of the VBA "With" blocks. Each of these blocks formats one side of a spreadsheet cell. The block you didn't delete adds a border to the cell's left side, which you'll see in the next few steps.

Click the "Excel" icon in the Windows taskbar to return to Excel. Click any blank cell, and then click the "Macros" button on the "Developer" tab. Double-click the name of your macro to run the macro. Notice that Excel painted just a left border on the cell you clicked. You'll now change the macro to paint the right border of the cell.

Use the instructions from step 4 to re-enter the development environment, and then double-click the text "xlEdgeLeft." Type the word "xlEdgeRight," and then return to Excel as you did in step 6.

Click a new cell, and then rerun your macro by following the step 6 instructions. Notice that the cell has a right border this time, not a left one.

Cite this Article A tool to create a citation to reference this article Cite this Article

About the Author

Darrin Koltow wrote about computer software until graphics programs reawakened his lifelong passion of becoming a master designer and draftsman. He has now committed to acquiring the training for a position designing characters, creatures and environments for video games, movies and other entertainment media.