Solved

Format Excel Cells using VBA Code

Posted on 2011-02-11
5
1,094 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
  • 3
  • 2
5 Comments
 
LVL 10

Expert Comment

by:cdebel
Comment Utility
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
Comment Utility
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:cdebel
Comment Utility
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
Comment Utility
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:
cdebel earned 250 total points
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now