asked on

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

nap0leon

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

ASKER CERTIFIED SOLUTION

andrewssd3

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Chris Bottomley

.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

byundt

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

apitech

ASKER

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".)

andrewssd3

How did it not work? - I tested my solution and it was fine, and byundt's looks bulletproof to me. What exactly goes wrong?

byundt

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

Chris Bottomley

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