Solved

Format Excel Cells using VBA Code

Posted on 2011-02-11
5
1,101 Views
Last Modified: 2012-05-11
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
Comment
Question by:srikanthv2322
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 34876884
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
 

Author Comment

by:srikanthv2322
ID: 34880082
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
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 34882820
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
 

Author Comment

by:srikanthv2322
ID: 34921409
Its creating sytle applied to all the cells in the excel sheet, but i need if for say 8 records
0
 
LVL 10

Accepted Solution

by:
Christian de Bellefeuille earned 250 total points
ID: 34921540
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

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question