counting cells with borders or bold characters

[chaitanya]

hi sir,

how can i count cells that having four side border or the cells containing bold alpha numeric characters.

Thanking you.


chaitanya

 

[kaushik03]

Hi Chaitanya,


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


Regarding uploading file,please see the below link:

http://chandoo.org/forums/topic/posting-a-sample-workbook


Now regarding your question:


Say your data is as follows from A1 to A5 in sheet1:


a

d

r2

ty5

tr5


Note: A1,A3,A4 have four side boarders and A2,A4,A5 are bolded


Press ALT+F11 to bring VB editor,insert a module and copy paste the below code:

[pre]

Function CntBld(R As Range) As Long
Dim cell As Range
Dim myCount As Long
Application.Volatile
For Each cell In R
If cell.Font.Bold = True Then
myCount = myCount + 1
End If
Next cell

CntBld = myCount
End Function

Function CntAllBrds(rng As Range)

Dim i As Integer
Dim cell As Range

i = 0

For Each cell In rng
If cell.Borders(xlEdgeRight).LineStyle <> xlLineStyleNone And         cell.Borders(xlEdgeBottom).LineStyle <> xlLineStyleNone And cell.Borders(xlEdgeTop).LineStyle <> xlLineStyleNone And cell.Borders(xlEdgeLeft).LineStyle <> xlLineStyleNone Then
i = i + 1
End If
Next

CntAllBrds = i
End Function

[/pre]
Now go back to your sheet and write =CntBld(A1:A5) which will give you number of cells are bolded in range specified and =CntAllBrds(A1:A5) which will give you number of cells are boarders(four side boarders) in range specified.


Note: If you change the formating of any cells(w.r.t. bold/unboald, removing boarder or add boarders to other cells in that range), formula will not update automatically, you have to click back on the cell where you have typed the formula and need to press enter to update the formula.


I am working on the autoupdate part and will share with you soon,


Kaushik