Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post

    Count based on formatting (Excel 2000 SR-1)

    Does anyone know if there is quick way to count items in a column, or named range if necessary, that do not have a bold format applied to them. In this list I built, I have used bold to format Categories and then listed the sub-categories under them unbolded. I simply want to count the non-bold entires. I have looked at a number of the different Count functions and it is not obvious whether they can do this, or do I have to have a small macro that uses the IF function. Any insight that anyone can provide is greatly appreciated. Thanks.

    Ron M <img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count based on formatting (Excel 2000 SR-1)

    You'll have to use a user-defined VBA function for this:

    Function CountNotBold(MyRange As Range) As Long
    Dim oCell As Range
    Dim lngCount As Long
    For Each oCell In MyRange
    If oCell.Font.Bold = False Then
    lngCount = lngCount + 1
    End If
    Next oCell
    CountNotBold = lngCount
    Set oCell = Nothing
    End Function

    To count the number of not-bolded cells in A3:A25, put the formula =CountNotBold(A3:A25) in a cell.

    Notes:
    You could use a function CountBold that counts the bolded cells instead, and subtract this from CountA(range).
    The function CountNotBold includes empty cells in its count. If you want to ignore empty cells, build in an extra check.

  3. #3
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count based on formatting (Excel 2000 SR-1)

    edited - Hans was faster and more through. -Use his <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

    Ron,
    You can create a funtion to test for bold. See the attached workbook. The count the False hits.

    Function CellBold(Cell)
    Application.Volatile
    CellBold = Cell.font.Bold
    End Function

    Hope this helps,
    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  4. #4
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Count based on formatting (Excel 2000 SR-1)

    Thanks to both <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> Hans and Reimer. It was as I suspected. Hans, thanks for the VBA Function. Can I run it separately, or does it have to be inside a subroutine - still learning VBA <img src=/S/compute.gif border=0 alt=compute width=40 height=20>. Upon rereading, I see how to make it work. Many thanks...

    Ron M <img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count based on formatting (Excel 2000 SR-1)

    You can run Hans' function by just putting a formula like =CountNotBold(A3:A25) in a cell as he said in his message.

    One note about these functions. Changing the bold property of the font for a cell is not considered by Excel to be an event that causes a recalculate event. Therefore, if you have a formula like the one above in a cell, and you change the bold property of a cell or cells in the range A3:A25, the formula will not automatically recalculate. You will need to manually force a recalculate which can be done by selecting the cell containing the formula and then pressing F2 and Enter.
    Legare Coleman

  6. #6
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Count based on formatting (Excel 2000 SR-1)

    Legare, thanks for the caution on the recalculate.

    Hans, I have run the function and I get a #NAME? error associated with it. I am wondering if this is a function of how I maintain my macros and functions? I keep them in a spreadsheet personal.xls that I keep in the XLSTART folder. Do I need to have the function in the worksheet that uses it?

    Any insight you can provide is greatly appreciated. Thanks.

    Ron M

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count based on formatting (Excel 2000 SR-1)

    If the function is in a module in the active workbook, you can use =CountNotBold(...)
    If the function is in a module in another workbook, you must prefix it with the workbook name and !, in this case =Personal.xls!CountNotBold(...)
    If the function is in a module in an add-in (.xla), you can use =CountNotBold(...), just as if it was in the active workbook.

  8. #8
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Count based on formatting (Excel 2000 SR-1)

    Thanks Hans. That helps to clarify the situation.

    Regards,
    Ron

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •