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.
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.
Step 4
You could see that the macro has drawn border for each of the filled cells in our sample data.
If you want to draw border around each of the cells in a range and not based on any condition the just delete the if condition from the macro.
Hope this helped.