Excel vba code, macro

[Shubham_Kinge]

Can someone give me the code of macro which input should be like - range like A1:K100
and can change whole sheet font to times new roman, should bold the headers and last row. need to auto adjust the full columns and gridlines should be invisible with A1:K100 as full borders.

Thnaks in advance

[arjunshenoy]

Hi @Shubham_Kinge

Please find the required macro where you can specify the range accordingly:

Sub FormatSheet()

    Dim myRange As Range
    
    ' Prompt the user to enter a range
    On Error Resume Next
    Set myRange = Application.InputBox("Enter range to format:", Type:=8)
    On Error GoTo 0
    
    ' Check if a range was selected, and exit the sub if not
    If myRange Is Nothing Then
        Exit Sub
    End If
    
    ' Set font to Times New Roman
    Cells.Font.Name = "Times New Roman"
    
    ' Bold the headers
    With myRange.Rows(1)
        .Font.Bold = True
    End With
    
    ' Bold the last row
    With myRange.Rows(myRange.Rows.Count)
        .Font.Bold = True
    End With
    
    ' Auto adjust columns
    myRange.Columns.AutoFit
    
    ' Set full borders for the specified range
    With myRange
        .Borders.LineStyle = xlContinuous
        .Borders.Weight = xlThin
    End With
    
    ' Set gridlines to invisible for the specified range
    ActiveWindow.DisplayGridlines = False
    
    Range("A1:K1").Select
    Selection.Font.Bold = True
    Range("A100:K100").Select
    Selection.Font.Bold = True
    
    Cells.Select
    Cells.EntireColumn.AutoFit
    
End Sub

Hope this helps,
Best Regards.