VBA & Macro Problems

How to Count Cells with Four-Sided Borders Using Excel VBA

Anum MushtaqAnum Mushtaq Nov 11, 2026 869 views

Question details

The user wants to create a reusable VBA function to count the number of cells within a range that have borders on all four sides, with options to exclude blank cells or check specific border colors.

How to Count Cells with Four-Sided Borders Using Excel VBA
Product
Excel
Device & OS
not provided
Scenario
Counting specifically formatted cells (with full borders) for data analysis or auditing purposes.
Observed behavior
The user needs a customized VBA macro because built-in Excel functions cannot inherently count cells based on border formatting.
Before you start

Before using VBA, ensure that you have enabled the Developer tab in your spreadsheet software and saved your workbook as a Macro-Enabled Workbook (.xlsm) so your code is not lost upon exiting.

Solution 1Recommended

Create a Custom VBA Function to Count Bordered Cells

Use this comprehensive VBA script to build a custom formula (=CountBordered) that checks for four-sided borders, with optional parameters for skipping blanks and checking border colors.

Native formulas like COUNTIF do not support checking cell formatting such as borders. By writing a User Defined Function (UDF) in VBA, you can evaluate formatting properties such as xlEdgeTop, xlEdgeBottom, xlEdgeLeft, and xlEdgeRight to count matching cells dynamically.

1
Open the VBA Editor

Press the ALT + F11 keys simultaneously to open the Visual Basic for Applications (VBA) Editor.

2
Insert a New Module

Click on 'Insert' in the top menu bar and select 'Module' to create a new blank module for your code.

3
Paste the VBA Code

Copy and paste the following VBA code into the module window: Function CountBordered(Rng As Range, Optional SkipBlank As Boolean = False, Optional LineColor As Variant) As Long Dim Cel As Range For Each Cel In Rng If Cel.Borders(xlEdgeTop).LineStyle <> xlLineStyleNone And _ Cel.Borders(xlEdgeLeft).LineStyle <> xlLineStyleNone And _ Cel.Borders(xlEdgeBottom).LineStyle <> xlLineStyleNone And _ Cel.Borders(xlEdgeRight).LineStyle <> xlLineStyleNone And _ (Not SkipBlank Or Len(Cel.Value) > 0) Then If IsMissing(LineColor) Or _ (Cel.Borders(xlEdgeTop).Color = LineColor And _ Cel.Borders(xlEdgeLeft).Color = LineColor And _ Cel.Borders(xlEdgeBottom).Color = LineColor And _ Cel.Borders(xlEdgeRight).Color = LineColor) Then CountBordered = CountBordered + 1 End If End If Next Cel End Function

4
Use the Function in Your Worksheet

Close the VBA Editor. In any cell, type `=CountBordered(A1:C6)` to count bordered cells. Use `=CountBordered(A1:C6, TRUE)` to exclude empty cells, or `=CountBordered(A1:C6, , 255)` to specifically count cells with red borders.

Create a Custom VBA Function to Count Bordered Cells
Understanding Color Codes: In VBA color codes, 255 represents red, 65280 is green, and 16711680 is blue. You can adjust the LineColor parameter based on the specific border color you wish to count.

Run Advanced VBA Macros Easily with WPS Spreadsheet

WPS Office offers comprehensive support for Excel macros and VBA scripts. You can seamlessly write, edit, and execute custom functions like counting bordered cells directly within WPS Spreadsheet.

  1. 1. Open Your Workbook: Launch WPS Spreadsheet and open the document where you want to count bordered cells.
  2. 2. Access the Developer Tools: Navigate to the Tools tab and click on Macro, or press ALT + F11 to launch the built-in VBA Editor.
  3. 3. Add and Save the Macro: Insert a new module, paste your custom function code, and remember to save the document as a Macro-Enabled Workbook (.xlsm).
Fully compatible with Microsoft Excel (.xlsm) macro workbooks.Includes a built-in VBA Editor for creating custom formatting functions.Lightweight software with fast execution for heavy data processing.Free alternative with a highly familiar user interface.
microsoft office alternative - wps office

Frequently Asked Questions

Can I use a regular formula instead of VBA to count bordered cells?

No, standard spreadsheet functions (like COUNTIF) only evaluate cell values and cannot read cell formatting such as borders, background colors, or font colors. A custom VBA function (UDF) is required to evaluate formatting.

How do I save my workbook after adding the VBA function?

You must save your file as a Macro-Enabled Workbook (.xlsm). If you save it as a standard .xlsx file, the VBA code will be permanently deleted when the file is closed.

How can I modify the VBA code to check cell background color instead of border color?

To check for background color, you can change the evaluated property in the code from `Cel.Borders(xlEdgeTop).Color` to `Cel.Interior.Color = FillColor`.

Will this custom function update automatically if I add or remove a cell border?

No, changing a cell's formatting does not automatically trigger a formula recalculation. You will need to press F9 or double-click the formula cell and press Enter to force the function to recount the bordered cells.