Apply cell formatting using VBA

[Isis]

I am producing CSV files (in a seprerate app) and importing these into
Excel then saving them out as xls files - that all works well.

I have a need to format the data to make it look better and I would like to
auttomate this using VBA.

I will want to set column widths for my columns (mostly less than 10) and
apply formatting to the columns containing numbers.

I also need to center and bold various header rows that occur throughout
and bold some individual columns.

There are not usually more that 100 rows so speed concerns probably do not
apply here.

Although there are a number of different formats of sheets (requiring
different groups of the formatting mentioned above) - I will be able
identify what these sheets are based on the identification of a single cell
in the topr right (or somewhere).

I have done a fair amount of messing with actual data values ion cells
using VBA code like this;

'For Each lCell In Cells.SpecialCells(xlTextValues)
For Each lCell In Range("A2:N100").SpecialCells(xlTextValues)
lCell.Formula = StrConv(lCell.Formula, vbProperCase)
Next
Next lws


but I have not done any formatting of attributes like bold and number
formatting etc.

I am not sure how to bold and center a whole row in code and how that would
fit with other code that I am using too format numeric formats etc.

I am NOT in any way asking someone to do all this for me, I am happy to put
the work in if anyone can give me a few pointers and start me on the way -
I can always come back if I get stuck !

Any help appreciated.

Thanks

 

[Ben McClave]

One thing that I usually do is record a macro where I apply all of the formatting I want and then adapt that macro to take a range argument. For example, I turned on the macro recorder and then applied a bold font, centered the text, and used autofit to adjust the column size (all without changing the selected cells). After a few tweaks, I changed the recorded macro to take a range argument and called it FormatHeader. Now, I can feed the macroany range using the syntax:

FormatHeader Range("A1:H1")

You could record similar macros for any other types of formatting that you need. Then it becomes a simple matter of feeding the correct range to the macros.

One note of caution, even though you do not have a lot of rows of data, I'dbe careful about applying formatting to an entire row or column as it can cause performance issues. Rather than using

FormatHeader Rows(1)

you might try

FormatHeader Range(Cells(1, 1), Cells(1, Sheet1.UsedRange.Columns.Count))

Here is the code I mentioned above:

Sub FormatHeader(rHeaders As Range)

With rHeaders
.Font.Bold = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.EntireColumn.AutoFit
End With

End Sub

 

[Ron Rosenfeld]

I am producing CSV files (in a seprerate app) and importing these into
Excel then saving them out as xls files - that all works well.

I have a need to format the data to make it look better and I would like to
auttomate this using VBA.

I will want to set column widths for my columns (mostly less than 10) and
apply formatting to the columns containing numbers.

I also need to center and bold various header rows that occur throughout
and bold some individual columns.

There are not usually more that 100 rows so speed concerns probably do not
apply here.

Although there are a number of different formats of sheets (requiring
different groups of the formatting mentioned above) - I will be able
identify what these sheets are based on the identification of a single cell
in the topr right (or somewhere).

I have done a fair amount of messing with actual data values ion cells
using VBA code like this;

'For Each lCell In Cells.SpecialCells(xlTextValues)
For Each lCell In Range("A2:N100").SpecialCells(xlTextValues)
lCell.Formula = StrConv(lCell.Formula, vbProperCase)
Next
Next lws


but I have not done any formatting of attributes like bold and number
formatting etc.

I am not sure how to bold and center a whole row in code and how that would
fit with other code that I am using too format numeric formats etc.

I am NOT in any way asking someone to do all this for me, I am happy to put
the work in if anyone can give me a few pointers and start me on the way -
I can always come back if I get stuck !

Any help appreciated.

Thanks

Column widths and various attributes can be set on ranges.
Column width

example:

=========================
Option Explicit
Sub foo()
Dim r As Range, c As Range
Set r = Sheet1.UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues)
For Each c In r
c.Value = StrConv(c.Text, vbProperCase)
Next c
With r
.Font.Bold = True
.EntireColumn.AutoFit
End With

End Sub
=========================