My Blog

Formatting Text

1

In continuation from here

Now let’s try and make our data above more presentable.

The first thing that we notice that the text in the cells going beyond the cell columns. So let’s fix that. In fact the following are few of the things that we will be doing to make our data presentable.

  1. Autofit Columns
  2. Shading the TITLES (Month, Money Spent, Total Expenses and Average Expenses”) to Black
  3. Change the font color of the title to White
  4. Formatting the amount to display as $####.##
  5. And finally create borders

See this code:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    '~~> Add a New Workbook
    xlWorkBook = xlApp.Workbooks.Add

    '~~> Display Excel
    xlApp.Visible = True

    '~~> Set the relebant sheet that we want to work with
    xlWorkSheet = xlWorkBook.Sheets("Sheet1")

    With xlWorkSheet
        '~~> Directly type the values that we want
        .Range("A1").Value = "Month"
        .Range("A2").Value = "January"
        .Range("A3").Value = "February"
        .Range("A4").Value = "March"
        .Range("A5").Value = "April"

        .Range("B1").Value = "Money Spent"
        .Range("B2").Value = "1000.00"
        .Range("B3").Value = "1500.00"
        .Range("B4").Value = "1200.00"
        .Range("B5").Value = "1100.00"

        '~~> Set title for Total and Average Expenses
        .Range("A6").Value = "Total Expense"
        .Range("A7").Value = "Average Expense"

        '~~> Insert formulas
        .Range("B6").Formula = "=Sum(B2:B5)"
        .Range("B7").Formula = "=Average(B2:B5)"

        '~~> Shade the titles
        With .Range("A1:B1,A6:A7")
            .Interior.ColorIndex = 1 '<~~ Cell Back Color Black
            With .Font
                .ColorIndex = 2 '<~~ Font Color White
                .Size = 8
                .Name = "Tahoma"
                .Underline = Excel.XlUnderlineStyle.xlUnderlineStyleSingle
                .Bold = True
            End With
        End With

        '~~> Set the number format
        .Range("B2:B7").NumberFormat = "$#,##0.00"

        '~~> Create Borders
        With .Range("A1:B7")
            With .Borders(Excel.XlBordersIndex.xlEdgeLeft)
                .LineStyle = Excel.XlLineStyle.xlDouble
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = Excel.XlBorderWeight.xlThin
            End With
            With .Borders(Excel.XlBordersIndex.xlEdgeTop)
                .LineStyle = Excel.XlLineStyle.xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = Excel.XlBorderWeight.xlThin
            End With
            With .Borders(Excel.XlBordersIndex.xlEdgeBottom)
                .LineStyle = Excel.XlLineStyle.xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = Excel.XlBorderWeight.xlThin
            End With
            With .Borders(Excel.XlBordersIndex.xlEdgeRight)
                .LineStyle = Excel.XlLineStyle.xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = Excel.XlBorderWeight.xlThin
            End With
            With .Borders(Excel.XlBordersIndex.xlInsideVertical)
                .LineStyle = Excel.XlLineStyle.xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = Excel.XlBorderWeight.xlThin
            End With
            With .Borders(Excel.XlBordersIndex.xlInsideHorizontal)
                .LineStyle = Excel.XlLineStyle.xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = Excel.XlBorderWeight.xlThin
            End With
        End With

        '~~> Autofitting text in columns
        .Columns("A:B").EntireColumn.AutoFit()
    End With
End Sub

When you run the code, this is the output that you will get.

  1. Eric Nguyen
    Eric Nguyen12-06-2013

    Siddharth,

    You’ve showed how to format for dollars; however, could you show us how to format for other options? I’m creating a time log and want to format my cells so that they look like H:MM AM/PM without going into excel and having to format it manually.

    Thanks! Your tutorials have been of great help thus far