Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Oct 2001
    Location
    Ellettsville, Indiana, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formatting Subtotals (2000)

    When you create subtotals for a data list it enters the subtotal text in bold but not the values. Is that a code you can be written to quickly format all the subtotal values once the subtotal has been done?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Formatting Subtotals (2000)

    Tanya,

    Once you created your subtotals, select the data list and run the following code :<pre>Sub BoldSubtotals()
    Dim FormulaCells As Range
    Dim oCell As Range
    Set FormulaCells = Selection.SpecialCells(xlCellTypeFormulas, 1)
    For Each oCell In FormulaCells
    If Left(oCell.Formula, 7) = "=SUBTOT" Then
    oCell.Font.Bold = True
    End If
    Next
    End Sub</pre>

    That should apply bold formatting to all cells containing a Subtotal Formula. You could keep the code in your personal.xls and assign it to a Toolbar button.

    Andrew C

  3. #3
    Star Lounger
    Join Date
    Oct 2001
    Location
    Ellettsville, Indiana, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting Subtotals (2000)

    Andrew....you rock! It works great. Thank you

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Formatting Subtotals (2000)

    If you are using "Data, Subtotals", a non-VBA way to do what Andrew demonstrates is to select the entire subtotal area including headers and grand total if applicable, collapse the subtotal outline to the subtotals, then select Edit, Go To, Special, Visible Cells Only, then Format them as you wish.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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