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

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