Excel VBA - Count Cells with Borders

[Compl9x]

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 cells that are completely surrounded by borders, with text in the cell.

3. Count cells that are completely surrounded by borders (Top, bottom, left, and right) that are a certain color (red)

I also need the formula for black and blue, but I'm assuming I can change the "Red" to a different color after.

Thanks for help on any of these formulas. I have very little experience with VBA, and all of my research and code I have written do not work.

[#2]

You will need VBA coding.

[#3]

@Compl9x 

Here is a function that does all three.

Function CountBordered(Rng As Range, Optional SkipBlank As Boolean, Optional FillColor) 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(FillColor) Then
                If Cel.Interior.Color = FillColor Then
                    CountBordered = CountBordered + 1
                End If
            Else
                CountBordered = CountBordered + 1
            End If
        End If
    Next Cel
End Function

To count cells that are surrounded by borders in A1:C6:

=CountBordered(A1:C6)

To count non-empty cells that are surrounded by borders in A1:C6:

=CountBordered(A1:C6, TRUE)

To count cells with red fill that are surrounded by borders in A1:C6:

=CountBordered(A1:C6, , 255)

To count non-empty cells with red fill that are surrounded by borders in A1:C6:

=CountBordered(A1:C6, TRUE, 255)

For green, use 65280, and for blue, use 16711680 instead of 255.

[#4]

Thank you so much! One clarification, I was looking for Border color, not fill color. Is this an easy change?

[#5 · Accepted solution]

@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

[#6]

That's perfect! You mentioned the numers for green and blue. Does it support the Excel default light blue as well?

[#7]

@Compl9x 

Yep. That's 15773696

How to find out?

Select a cell with the border color you want.

Activate the Visual Basic Editor.

Press Ctrl+G to activate the Immediate window.

Type

? ActiveCell.Borders(xlEdgeTop).Color

and press Enter.

You can copy this number and paste it into the formula.