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
"Neil Pearce" wrote:
Is there a way that I can count the number of cells in a range that have a
right border? Can this then be adapted to count either a certain colour
line, e.g. red, blue, black... or failing this a certain type of line, e.g.
dashed, dotted... ?
You help would be much appreciated.
Cheers,
Neil