How To Automatically Border Cell In Excel

In this post we will see how to auto border cell in excel.

Bordering cell manually is a time taking task and with the help of a small script in vba we could auto border cells in excel within seconds.

In this example I am going to explain how can we auto border all filled cell within a range in excel.

Below is a sample range in which some cells are filled with values and rest are empty and we want to auto border cells that have values in them.

Step 1

We will create a macro in excel to auto border all filled cells in a range in excel.

Open VB editor from Developer tab in excel as shown in the figure.

Step 2

Insert a module in which we will write a small macro to auto border cells in excel.

Step 3

Now double click the newly created module to open that and paste the below code in the module.

Sub CellBorder()

Set CD = ThisWorkbook.Worksheets(“Sheet1”)

Application.ScreenUpdating = False

Dim lngLstCol As Long, lngLstRow As Long

lngLstRow = CD.UsedRange.Rows.Count

lngLstCol = CD.UsedRange.Columns.Count

For Each rngCell In Range(Range(“B8:H20”), Cells(lngLstRow, lngLstCol))

With rngCell

If Len(Trim(.Value2)) = 0 Then

With .Borders

.LineStyle = xlNone

End With

Else

With .Borders

.LineStyle = xlContinuous

.Weight = xlThin

.ColorIndex = xlAutomatic

End With

End If

End With

Next

Application.ScreenUpdating = True

End Sub

I will explain what each line in the code is doing.

ScreenUpdating = False –This wont update the screen as the macro runs, you will be able to see the result after when the macro has finished running.
Dim lngLstCol As Long, lngLstRow As Long
lngLstRow = CD.UsedRange.Rows.Count
lngLstCol = CD.UsedRange.Columns.Count

The above 3 lines of code are to determine the no of times the loop will run based on the filled cells in a range.

For Each rngCell In Range(Range(“B8:H20”), Cells(lngLstRow, lngLstCol))

Loop will run through each of the filled cell.

If Len(Trim(.Value2)) = 0 Then
With .Borders
.LineStyle = xlNone
End With
Else
With .Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End If
End With
Next

The above codes are checking the cells with the help of an if condition and if the cell is filled, the macro is drawing border around that cell, if the cell is not filled it is not bordering that particular cell.

Application.ScreenUpdating = True –This will update the screen as the macro finishes.