How to Count Cells with Four-Sided Borders Using Excel VBA
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.

- 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 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.
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.
Press the ALT + F11 keys simultaneously to open the Visual Basic for Applications (VBA) Editor.
Click on 'Insert' in the top menu bar and select 'Module' to create a new blank module for your 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
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.

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. Open Your Workbook: Launch WPS Spreadsheet and open the document where you want to count bordered cells.
- 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. 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).

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.




