Macro VBA

Super Contributor

Hello Everyone,

I have written code and it works perfectly. 

like - 

Screenshot (6750).png

But I want the option "for which fontstypes of font, font size, Bold true or Bold false" to pop up as they enter EXCEL before run in YEARLY REPORT sheet.


So, what should i write in VBA code?

Please help..??


Here is a attach file..

4 Replies


Please try to explain more clearly and in detail what you want to do.

Before run in YEARLY REPORT sheet, it should give option to choose which fonts you want, what types of font you want, what font size you want to take, Either Bold or not Bold". After ask, then it should run the code.

So, what should i write?

Please help..


New version of the FormatData macro:

Sub FormatData()
' FormatData Macro

    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Style = "Currency"
End Sub
Thank you so much sir