VBA format a range of cells

Dave Patton

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

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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>
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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".
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top