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?

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" <Sean@discussions.microsoft.com> wrote in message
news:4EC21474-8644-4CE8-95DD-75C8D03DD607@microsoft.com...
> 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?
>

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?
>

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

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

Any chance you could expand on this please? I too need to count bold cells, but I am very limited in my knowledge of VB. I have some code in the worksheet already so I know how to set it up, but I need help writing the function specifically for my sheet.

I have columns L:AE which contain data.
In cell L90 I want to show the total number of cells in range L3:L84 which are bold, and so on across columns M:AE.

Can anyone help me set this up as I am having to do it manually at the moment.

Originally Posted by David Jessop

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?
>