Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1112
  • Last Modified:

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
0
srikanthv2322
Asked:
srikanthv2322
  • 3
  • 2
1 Solution
 
Christian de BellefeuilleProgrammerCommented:
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

0
 
srikanthv2322Author Commented:
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.
0
 
Christian de BellefeuilleProgrammerCommented:
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

0
 
srikanthv2322Author Commented:
Its creating sytle applied to all the cells in the excel sheet, but i need if for say 8 records
0
 
Christian de BellefeuilleProgrammerCommented:
If you run this piece of code after running your query, and that your query return only 8 records, then it should apply only to those 8 records.

UsedRange mean "cells with values in them".  So if your upper left cell containing some data is B7, and your bottom right corner cell containing some data is Z15, then it will apply this style to all the cells in this range, therefore: ONLY FROM B7 TO Z15.

I've just tested it myself.  I'm running a query from my SQL Server, it got 818 records in it, and 26 columns.  I've placed the result in A3, so i got data from A3 to Z821, and only those cell got this style.
If i type some text in A1 for example, this style doesn't apply to this cell since its out of this range.

If you say that it's not doing this, then you did something wrong somewhere.  Please post your files, and i'll help.  

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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