Link to home
Start Free TrialLog in
Avatar of srikanthv2322
srikanthv2322

asked on

Format Excel Cells using VBA Code

I want format the Cells in an excel sheet with the click of Command Button, mainly i needed to set Borders for Cells, Bold the Text in a Cell,Underline the Text, Change the font

Please provide me the VBA code snippets for each of the above

Thank You
Avatar of Christian de Bellefeuille
Christian de Bellefeuille
Flag of Canada image

Here's what you want... just call it from your button.

 
Sub DoIt()
    With Sheet1.Cells(1, 1)
        .Font.Bold = True
        .Font.Underline = True
        .Font.Name = "Time New Roman"
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        
    End With
End Sub

Open in new window

Avatar of srikanthv2322
srikanthv2322

ASKER

I want the above border and style properties to apply till the Row the data has been extracted through Query.

Ex: If i have 8 Records, the above style should apply till 8th row and not beyond that.
That request was not specified in your original question.  You asked to know how to do this to a cell, without specifying which one.

But here is how you would do that if you want to draw a border arround every cell corresponding to your request.

 
Sub DoIt()
    With Sheet1.UsedRange.Cells
        .Font.Bold = True
        .Font.Underline = True
        .Font.Name = "Time New Roman"
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        
        With .Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        
        With .Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
    End With
End Sub

Open in new window

Its creating sytle applied to all the cells in the excel sheet, but i need if for say 8 records
ASKER CERTIFIED SOLUTION
Avatar of Christian de Bellefeuille
Christian de Bellefeuille
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial