VBA format a range of cells

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,354
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
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
I guess you have to do a loop through each cell<pre>
For each Cll in Range("Call")
'Your code to change here, but
'change ActiveCell with Cll
Next Cll</pre>
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Sub CheckCells(CurrRange As Range)
For Each cell In CurrRange
If cell.Value < 0 Then cell.Font.Bold = True Else cell.Font.Bold = False
Next cell
End Sub


Sub BoldNegative()
If TypeName(Selection) <> "Range" Then Exit Sub
' If one cell is selected, check it and exit
If Selection.Count = 1 Then
CheckCells (Selection)
Exit Sub
End If
On Error Resume Next
' Check the cells with constants
Call CheckCells(Selection.SpecialCells(xlConstants, 23))
' Check the cells with formulas
Call CheckCells(Selection.SpecialCells(xlFormulas, 23))
End Sub


Note: Selection can be the actual current selection as a range or a named range! Hope these help. JSW
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,354
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Thanks Juan Pablo G. and Joe Was.

I used the For ...next
and it worked.

It was not fast but it worked.

Thanks again


I had difficulty connecting to the site.
Dave
 
L

Legacy 12630

Guest

ADVERTISEMENT

I don't think you need to loop through each cell.

Just try changing ActiveCell to Range("Cal") :-

With Range("Cal").Characters(Start:=1, Length:=12).Font
.FontStyle = "Bold"
.Size = 13
End With
With Range("Cal").Characters(Start:=13, Length:=18).Font
.FontStyle = "Regular"
.Size = 12
End With
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,354
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

On 2002-11-18 06:40, Ponsonby wrote:
I don't think you need to loop through each cell.

Just try changing ActiveCell to Range("Cal") :-

With Range("Cal").Characters(Start:=1, Length:=12).Font
.FontStyle = "Bold"
.Size = 13
End With
With Range("Cal").Characters(Start:=13, Length:=18).Font
.FontStyle = "Regular"
.Size = 12
End With


Thanks for the help.

I inititally used this type of Sub but it did not work. The change shows for a very short while and then the information reverts to how it was.

It may be because the cells contain text of diffferent lengths. All cells include the date as text dd-mmm-yyyy; some cells include additional text example "18-Nov-2002 Exec Mtg".
 
L

Legacy 12630

Guest
That's strange. I've just tested it and it works for me.
 

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
Yes, I tested with different strings of different lengths, and each one changed the first 13 characters. I tested that on 2000.
 
L

Legacy 12630

Guest
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,314
Messages
5,547,170
Members
410,775
Latest member
alal1030
Top