VBA format a range of cells

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,365
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Feb 15, 2002
Messages
4,365
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Watch MrExcel Video

Forum statistics

Threads
1,114,655
Messages
5,549,259
Members
410,905
Latest member
Extjel
Top