Here is a user-defined function that counts the number of cells with a right
border in a range. The range to check is the first argument, and is required.
An optional second argument lets you specify a color index (red = 3, blue =
5, etc.) and the function counts only cells in the range with a right border
matching that color.
Public Function CountBorders(Target As Range, _
Optional IndexColor As Variant) As Long
Dim Rng As Range, ColorTest As Boolean
CountBorders = 0
For Each Rng In Target
If Rng.Borders(xlEdgeRight).LineStyle <> xlNone Then
If IsMissing(IndexColor) Then
ColorTest = True
ElseIf Rng.Borders(xlEdgeRight).ColorIndex = IndexColor Then
ColorTest = True
Else
ColorTest = False
End If
If ColorTest = True Then
CountBorders = CountBorders + 1
End If
End If
Next Rng
End Function
Paste this code in a VBA code module in your workbook. Call the function
from your worksheet just like any other function:
=CountBorders(C4:E11) counts all right borders
=CountBorders(C4:E11,5) counts red right borders
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Hope this helps,
Hutch
