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.

sample data in excel to demonstrate how to auto border cells in excel




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.

open vb editor to write macro to auto border cells in excel

Step 2

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

insrte a module to write program 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.

code is pasted in the module to automatically border cells in excel

Step 4




You could see that the macro has drawn border for each of the filled cells in our sample data.

result showing that macro has dran borders around the cells

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.

Share The Knowledge

Random Posts

Leave a Reply