Border
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 |
Border
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 |
All times are GMT +1. The time now is 01:18 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com