COUNTIF BOLD

[Guest]

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?

 

[Bob Phillips]

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)

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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