• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

How to Have the Excel Macro Add a Bold Font

Hello:

Attached is a macro for my Excel 2010 .xltm file.  The following lines of code create totals:

Range("H" & Rows.Count).End(xlUp).Offset(1, 0).Resize(1, 3).FormulaR1C1 = "=sum(R2C:R[-1]C)"

Range("P" & Rows.Count).End(xlUp).Offset(1, 0).Resize(1, 3).FormulaR1C1 = "=sum(R2C:R[-1]C)"

Now, I need to have those totals be in bold font.  What code can I put in to this macro code to accomplish that?

Remember this code was not created from scratch.  Excel created this code, when I recorded my keystrokes as a macro and saved the file as an .xltm file.

I just want to make the results of the two lines of code I mentioned above to be bold.

I tried to put in lines of code to do this myself, but it did not work.  I tried such things as "Selection.Font.Bold = True" and so forth.  Again, no success.

Any help is much appreciated!  Thanks!

John
Bold.txt
0
apitech
Asked:
apitech
  • 2
  • 2
  • 2
  • +2
1 Solution
 
nap0leonCommented:
Two options:
1- re-record your macro and make the cell BOLD while you are recording it
2- try adding something like this which sets cell "B6" to bold.:
    Range("B6").Select
    Selection.Font.Bold = True
0
 
andrewssd3Commented:
Try this:
    With Range("H" & Rows.Count).End(xlUp).Offset(1, 0).Resize(1, 3)
        .FormulaR1C1 = "=sum(R2C:R[-1]C)"
        .Font.Bold = True
    End With

    With Range("P" & Rows.Count).End(xlUp).Offset(1, 0).Resize(1, 3)
        .FormulaR1C1 = "=sum(R2C:R[-1]C)"
        .Font.Bold = True
    End With

Open in new window

0
 
Chris BottomleyCommented:
.font.bold = true to set it therefore:

Range("H" & Rows.Count).End(xlUp).Offset(1, 0).Resize(1, 3).font.bold = true
Range("P" & Rows.Count).End(xlUp).Offset(1, 0).Resize(1, 3).font.bold = true

Chris
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
byundtCommented:
I rewrote your entire macro to integrate the font bolding as previously suggested andrewssd3 while eliminating the useless scrolling and duplicate column width setting statements. I also turned screen updating off. As a result, the macro should run instantly and without screen flicker.

Brad
Sub ClientRevenueSum()
'
' ClientRevenueSum Macro
'
    Application.ScreenUpdating = False
    With Range("H" & Rows.Count).End(xlUp).Offset(1, 0).Resize(1, 3)
        .FormulaR1C1 = "=SUM(R2C:R[-1]C)"
        .Font.Bold = True
    End With
    With Range("G" & Rows.Count).End(xlUp).Offset(1, 0)
        .FormulaR1C1 = "Totals"
        .Font.Bold = True
    End With
    With Range("P" & Rows.Count).End(xlUp).Offset(1, 0).Resize(1, 3)
        .FormulaR1C1 = "=SUM(R2C:R[-1]C)"
        .Font.Bold = True
    End With
    Columns("H:H").ColumnWidth = 15.29
    Columns("I:I").ColumnWidth = 14.43
    Columns("J:J").ColumnWidth = 14.86
    Columns("P:P").ColumnWidth = 14.57
    Columns("Q:Q").ColumnWidth = 19.29
    Columns("R:R").ColumnWidth = 20.86
End Sub

Open in new window

0
 
apitechAuthor Commented:
I tried all of these suggestions.  None of them worked, I'm afraid.  (The first suggestion by napoleon does not work, because the summing could occur in any row--not just "B6".)
0
 
andrewssd3Commented:
How did it not work? - I tested my solution and it was fine, and byundt's looks bulletproof to me. What exactly goes wrong?
0
 
byundtCommented:
If the code doesn't work, then the problem is with your data layout. You need to identify a column that always contains data (or header labels). The total row will go just below the last number or text in that column.

If you aren't comfortable modifying the code as required, please post a sample workbook so we can understand the data layout.

I modified the code so it uses column A for that purpose:
Sub ClientRevenueSum()
'
' ClientRevenueSum Macro
'
    Dim n As Long
    Application.ScreenUpdating = False
    n = Cells(Rows.Count, "A").End(xlUp).Row    'Rreference to column A should be to any column that always contains data. Sum row will be just below this.
    
    With Cells(n + 1, "G")
        .FormulaR1C1 = "Totals"
        .Font.Bold = True
    End With
    With Cells(n + 1, "H").Resize(1, 3)
        .FormulaR1C1 = "=SUM(R2C:R[-1]C)"
        .Font.Bold = True
    End With
    With Cells(n + 1, "P").Resize(1, 3)
        .FormulaR1C1 = "=SUM(R2C:R[-1]C)"
        .Font.Bold = True
    End With
    Columns("H:H").ColumnWidth = 15.29
    Columns("I:I").ColumnWidth = 14.43
    Columns("J:J").ColumnWidth = 14.86
    Columns("P:P").ColumnWidth = 14.57
    Columns("Q:Q").ColumnWidth = 19.29
    Columns("R:R").ColumnWidth = 20.86
End Sub

Open in new window

0
 
Chris BottomleyCommented:
Your code was totally untouched when adding teh two lines I said ... regardless of tidier code that others posted this does work when added to the code ... just as is the case with the post by andrewssd3

What does not work when you coded it ... how did you code it, i.e. try supplying teh full code?

Chris
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now