Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Format Excel Cells using VBA Code

Posted on 2011-02-11
5
Medium Priority
?
1,102 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 1000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

705 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