Excel VBA - Count Cells with Borders

Hello, I'm looking to create 3 new functions via Excel VBA which solve the following purposes: 1. Count cells that are completely surrounded by borders (Top, bottom, left, and right) 2. Count ce...

developer

excel

Formulas and Functions

Macros and VBA

HansVogelaar

May 07, 2024

Compl9x

Yes, that's a minor modification:

Code:

Function CountBordered(Rng As Range, Optional SkipBlank As Boolean, Optional LineColor) 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 _
                (Cel.Value <> "" Or Not SkipBlank) Then
            If Not IsMissing(LineColor) Then
                If 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
            Else
                CountBordered = CountBordered + 1
            End If
        End If
    Next Cel
End Function

Forum Discussion

Excel VBA - Count Cells with Borders

Resources