Home |
Search |
Today's Posts |
#1
|
|||
|
|||
COUNTIF BOLD
Dear Work Group,
I am trying to count the number of cells within a work sheet where the contents are in bold. The context in which I am using this in an archery score sheet where the gold in the centre of the target has two concentric circles. Both have the value 9 but the arrows in the inner circle are counted if there is a draw and the person with the mist inners will then be the winner. I use a simple COUNTIF(E4:G4,"9") to count the Golds but am having trouble counting the inner golds. I resolved to having the inner 9's represented by having them in bold so that this would not affect the normal scoring but I cannot find any means of counting characters or cells that are in bold. Can you assist me please? |
#2
|
|||
|
|||
Sean,
You need VBA to count bold cells. Here is a simple example Dim i As Long Dim cLastRow As Long Dim cBold As Long cLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To cLastRow If Cells(i, "A").Font.Bold Then cBold = cBold + 1 End If Next i Range("A1").EntireRow.Insert Range("A1").Value = "Num bold cells = " & cBold -- HTH RP (remove nothere from the email address if mailing direct) "Sean" wrote in message ... Dear Work Group, I am trying to count the number of cells within a work sheet where the contents are in bold. The context in which I am using this in an archery score sheet where the gold in the centre of the target has two concentric circles. Both have the value 9 but the arrows in the inner circle are counted if there is a draw and the person with the mist inners will then be the winner. I use a simple COUNTIF(E4:G4,"9") to count the Golds but am having trouble counting the inner golds. I resolved to having the inner 9's represented by having them in bold so that this would not affect the normal scoring but I cannot find any means of counting characters or cells that are in bold. Can you assist me please? |
#3
|
|||
|
|||
Hi,
I don't think you can do this easily. One simple way is to write a small function: Option Explicit Function CountBold(CellRef As Range) Dim r As Integer, c As Integer CountBold = 0 For r = 1 To CellRef.Rows.Count For c = 1 To CellRef.Columns.Count If CellRef.Cells(r, c).Font.Bold Then CountBold = CountBold + 1 Next c Next r End Function so =CountBold(A1:C29) would have the desired effect HTH, David Jessop "Sean" wrote: Dear Work Group, I am trying to count the number of cells within a work sheet where the contents are in bold. The context in which I am using this in an archery score sheet where the gold in the centre of the target has two concentric circles. Both have the value 9 but the arrows in the inner circle are counted if there is a draw and the person with the mist inners will then be the winner. I use a simple COUNTIF(E4:G4,"9") to count the Golds but am having trouble counting the inner golds. I resolved to having the inner 9's represented by having them in bold so that this would not affect the normal scoring but I cannot find any means of counting characters or cells that are in bold. Can you assist me please? |
#4
|
|||
|
|||
Hi
Press Alt+F11. Insert Module. Copy and Paste the below. Public Function FontBold(MyCell As Range) As Variant FontBold = MyCell.Font.Bold End Function Now, try your new formula: =FontBold(A1) Ola |
#5
|
|||
|
|||
Many thanks to you all for posting your solutions, these are much appreciated.
I can now have an all singing and all dancing scoresheet to make my life a lot easier. Again, many thanks, Seán |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif | Excel Worksheet Functions | |||
Countif - Countif | Excel Worksheet Functions | |||
Countif, Sumif, If - help! | Excel Worksheet Functions | |||
countif, again | Excel Worksheet Functions | |||
combining countif formulas | Excel Worksheet Functions |