VBA format a range of cells

[Dave Patton]

Excel 97 Win98

How can I format the entire range named “Cal”?
The range is now is B3:H75

I want the first line the default format (bold and font.size=13) and the second line
(regular and font size=12)

The following will format one cell<pre>Sub Macro1()

With ActiveCell.Characters(Start:=1, Length:=12).Font
.FontStyle = "Bold"
.Size = 13
End With
With ActiveCell.Characters(Start:=13, Length:=18).Font
.FontStyle = "Regular"
.Size = 12
End With
End Sub</pre>


Some cells have just 12 characters of text and the default is OK.

Other cells contain more than 12. The first 12 s/b unchanged and other say from 13
with Regular and font=12.
This message was edited by Dave Patton on 2002-11-17 20:37

 

[Dave Patton]

On 2002-11-18 16:18, Ponsonby wrote:
Have tested again and there is a problem, but don't know why.

Everyting works if all cells are longer than 12 characters.

If some cells are only 12 characters, then it works up to the last line of code (.Size=12) but when that line is run, the charcters after character 12 revert to Bold instead of Regular.

One way around this(I think) is to change the sequence of the code :-

With Range("Cal").Font
.FontStyle = "Regular"
.Size = 12
End With
With Range("Cal").Characters(Start:=1, Length:=12).Font
.FontStyle = "Bold"
.Size = 13
End With
This message was edited by Ponsonby on 2002-11-18 16:24

Excelllent! That worked.

Thanks Dave