Hi Everyone,

I have been given a Spreadsheet which needs Updating.
The Current ( Default on Excel for the Person that the Sheet Belongs
to ) Font is Set to "Ariel". I have Managed to Change the Column
Letters and Row Numbers from "Ariel" to "Tahoma" by using "Format",
"Style" and then Modifying the Font.
All the Cells in the Spreadsheet ( and Workbook ) that have
Information and Formulas in are Either "Tahoma" or "Comic Sans MS"
Font.
What I would Ideally like is a way of Changing ALL the Empty "Ariel"
Cells ( A1:IV65536) to Empty "Tahoma" Cells in Both the Worksheet AND
the Workbook.
I would like a Method of Achieving this Manually AND with a Macro if
Possible Please.
I am using XP and XL2002.

All the Best
Paul

Hi Paul,

Manually:

Select a single cell | Hit the F5 function key | Special | Check Blanks | OK
With the blank cells now selected, apply your desired formatting.

Repeat for each worksheet.

Programmatically, try:

Sub Tester()
Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
On Error Resume Next
sh.Cells.SpecialCells(xlBlanks).Font.Name = "Tahoma"
On Error GoTo 0
Next sh
End Sub

---
Regards,
Norman



"Paul Black" wrote in message
...
Hi Everyone,

I have been given a Spreadsheet which needs Updating.
The Current ( Default on Excel for the Person that the Sheet Belongs
to ) Font is Set to "Ariel". I have Managed to Change the Column
Letters and Row Numbers from "Ariel" to "Tahoma" by using "Format",
"Style" and then Modifying the Font.
All the Cells in the Spreadsheet ( and Workbook ) that have
Information and Formulas in are Either "Tahoma" or "Comic Sans MS"
Font.
What I would Ideally like is a way of Changing ALL the Empty "Ariel"
Cells ( A1:IV65536) to Empty "Tahoma" Cells in Both the Worksheet AND
the Workbook.
I would like a Method of Achieving this Manually AND with a Macro if
Possible Please.
I am using XP and XL2002.

All the Best
Paul

Hi Norman,

Thanks for the Reply.
I First tried the Manual Method, and this did work Between Cells A1
and the Last Active Cell, But it Ignored Everything Between the Last
Active Cell and Cell IV65536.
I then tried the Macro and Got Exactly the Same Results.

All the Best
Paul



"Norman Jones" wrote in message ...
Hi Paul,

Manually:

Select a single cell | Hit the F5 function key | Special | Check Blanks | OK
With the blank cells now selected, apply your desired formatting.

Repeat for each worksheet.

Programmatically, try:

Sub Tester()
Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
On Error Resume Next
sh.Cells.SpecialCells(xlBlanks).Font.Name = "Tahoma"
On Error GoTo 0
Next sh
End Sub

---
Regards,
Norman



"Paul Black" wrote in message
...
Hi Everyone,

I have been given a Spreadsheet which needs Updating.
The Current ( Default on Excel for the Person that the Sheet Belongs
to ) Font is Set to "Ariel". I have Managed to Change the Column
Letters and Row Numbers from "Ariel" to "Tahoma" by using "Format",
"Style" and then Modifying the Font.
All the Cells in the Spreadsheet ( and Workbook ) that have
Information and Formulas in are Either "Tahoma" or "Comic Sans MS"
Font.
What I would Ideally like is a way of Changing ALL the Empty "Ariel"
Cells ( A1:IV65536) to Empty "Tahoma" Cells in Both the Worksheet AND
the Workbook.
I would like a Method of Achieving this Manually AND with a Macro if
Possible Please.
I am using XP and XL2002.

All the Best
Paul

Sub Tester()
Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
On Error Resume Next
sh.Cells.Font.Name = "Tahoma"
On Error GoTo 0
Next sh
End Sub

Although, it seems to me if you set the font for the normal style to Tahoma,
it should make that the default for blank cells unless the have previously
been set to a different font.

Another think you could do is go to a sheet that is formatted the way you
want it. click on the intersection and do Edit=Copy, go to this sheet,
select A1 and do Edit=PasteSpecial and select formats. If that does more
than you want, you can close the workbook without saving changes.

--
Regards,
Tom Ogilvy


"Paul Black" wrote in message
...
Hi Norman,

Thanks for the Reply.
I First tried the Manual Method, and this did work Between Cells A1
and the Last Active Cell, But it Ignored Everything Between the Last
Active Cell and Cell IV65536.
I then tried the Macro and Got Exactly the Same Results.

All the Best
Paul



"Norman Jones" wrote in message
...
Hi Paul,

Manually:

Select a single cell | Hit the F5 function key | Special | Check Blanks
| OK
With the blank cells now selected, apply your desired formatting.

Repeat for each worksheet.

Programmatically, try:

Sub Tester()
Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
On Error Resume Next
sh.Cells.SpecialCells(xlBlanks).Font.Name = "Tahoma"
On Error GoTo 0
Next sh
End Sub

---
Regards,
Norman



"Paul Black" wrote in message
...
Hi Everyone,

I have been given a Spreadsheet which needs Updating.
The Current ( Default on Excel for the Person that the Sheet Belongs
to ) Font is Set to "Ariel". I have Managed to Change the Column
Letters and Row Numbers from "Ariel" to "Tahoma" by using "Format",
"Style" and then Modifying the Font.
All the Cells in the Spreadsheet ( and Workbook ) that have
Information and Formulas in are Either "Tahoma" or "Comic Sans MS"
Font.
What I would Ideally like is a way of Changing ALL the Empty "Ariel"
Cells ( A1:IV65536) to Empty "Tahoma" Cells in Both the Worksheet AND
the Workbook.
I would like a Method of Achieving this Manually AND with a Macro if
Possible Please.
I am using XP and XL2002.

All the Best
Paul

Hi Tom,

Thanks for the Reply.
Your Macro does change ALL the Blank Cells ( A1:IV65536 )to "Tahoma",
Unfortunately it also Changes Cells that have Data in. I Only want
Blank Cells to be Changed.

All the Best
Paul



"Tom Ogilvy" wrote in message ...
Sub Tester()
Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
On Error Resume Next
sh.Cells.Font.Name = "Tahoma"
On Error GoTo 0
Next sh
End Sub

Although, it seems to me if you set the font for the normal style to Tahoma,
it should make that the default for blank cells unless the have previously
been set to a different font.

Another think you could do is go to a sheet that is formatted the way you
want it. click on the intersection and do Edit=Copy, go to this sheet,
select A1 and do Edit=PasteSpecial and select formats. If that does more
than you want, you can close the workbook without saving changes.

--
Regards,
Tom Ogilvy


"Paul Black" wrote in message
...
Hi Norman,

Thanks for the Reply.
I First tried the Manual Method, and this did work Between Cells A1
and the Last Active Cell, But it Ignored Everything Between the Last
Active Cell and Cell IV65536.
I then tried the Macro and Got Exactly the Same Results.

All the Best
Paul



"Norman Jones" wrote in message
...
Hi Paul,

Manually:

Select a single cell | Hit the F5 function key | Special | Check Blanks
OK
With the blank cells now selected, apply your desired formatting.

Repeat for each worksheet.

Programmatically, try:

Sub Tester()
Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
On Error Resume Next
sh.Cells.SpecialCells(xlBlanks).Font.Name = "Tahoma"
On Error GoTo 0
Next sh
End Sub

---
Regards,
Norman



"Paul Black" wrote in message
...
Hi Everyone,

I have been given a Spreadsheet which needs Updating.
The Current ( Default on Excel for the Person that the Sheet Belongs
to ) Font is Set to "Ariel". I have Managed to Change the Column
Letters and Row Numbers from "Ariel" to "Tahoma" by using "Format",
"Style" and then Modifying the Font.
All the Cells in the Spreadsheet ( and Workbook ) that have
Information and Formulas in are Either "Tahoma" or "Comic Sans MS"
Font.
What I would Ideally like is a way of Changing ALL the Empty "Ariel"
Cells ( A1:IV65536) to Empty "Tahoma" Cells in Both the Worksheet AND
the Workbook.
I would like a Method of Achieving this Manually AND with a Macro if
Possible Please.
I am using XP and XL2002.

All the Best
Paul

Edit/Goto, click the Special button. Select Blanks. Then apply the formatting
you want. To do this in a macro, turn on the macro recorder, do it manually,
stop the recorder and look at the code it generated.


On 9 Sep 2004 15:04:30 -0700, (Paul Black) wrote:

Hi Tom,

Thanks for the Reply.
Your Macro does change ALL the Blank Cells ( A1:IV65536 )to "Tahoma",
Unfortunately it also Changes Cells that have Data in. I Only want
Blank Cells to be Changed.

All the Best
Paul



"Tom Ogilvy" wrote in message
...
Sub Tester()
Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
On Error Resume Next
sh.Cells.Font.Name = "Tahoma"
On Error GoTo 0
Next sh
End Sub

Although, it seems to me if you set the font for the normal style to
Tahoma,
it should make that the default for blank cells unless the have previously
been set to a different font.

Another think you could do is go to a sheet that is formatted the way you
want it. click on the intersection and do Edit=Copy, go to this sheet,
select A1 and do Edit=PasteSpecial and select formats. If that does more
than you want, you can close the workbook without saving changes.

--
Regards,
Tom Ogilvy


"Paul Black" wrote in message
...
Hi Norman,

Thanks for the Reply.
I First tried the Manual Method, and this did work Between Cells A1
and the Last Active Cell, But it Ignored Everything Between the Last
Active Cell and Cell IV65536.
I then tried the Macro and Got Exactly the Same Results.

All the Best
Paul



"Norman Jones" wrote in message
...
Hi Paul,

Manually:

Select a single cell | Hit the F5 function key | Special | Check Blanks
OK
With the blank cells now selected, apply your desired formatting.

Repeat for each worksheet.

Programmatically, try:

Sub Tester()
Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
On Error Resume Next
sh.Cells.SpecialCells(xlBlanks).Font.Name = "Tahoma"
On Error GoTo 0
Next sh
End Sub

---
Regards,
Norman



"Paul Black" wrote in message
...
Hi Everyone,

I have been given a Spreadsheet which needs Updating.
The Current ( Default on Excel for the Person that the Sheet Belongs
to ) Font is Set to "Ariel". I have Managed to Change the Column
Letters and Row Numbers from "Ariel" to "Tahoma" by using "Format",
"Style" and then Modifying the Font.
All the Cells in the Spreadsheet ( and Workbook ) that have
Information and Formulas in are Either "Tahoma" or "Comic Sans MS"
Font.
What I would Ideally like is a way of Changing ALL the Empty "Ariel"
Cells ( A1:IV65536) to Empty "Tahoma" Cells in Both the Worksheet AND
the Workbook.
I would like a Method of Achieving this Manually AND with a Macro if
Possible Please.
I am using XP and XL2002.

All the Best
Paul