Change Empty Cells Font for the Entire Workbook

[Paul Black]

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

 

[Norman Jones]

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

 

[Paul Black]

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

 

[Tom Ogilvy]

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.

 

[Paul Black]

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

 

[Myrna Larson]

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.

 

[Paul Black]

Thanks Myrna,

I tried your Suggestion, it Worked for ALL Blank Cells from "A1" to
the Last Cell with Something in. It Ignored from that Cell to Cell
IV65536.

All the Best
Paul

 

[Norman Jones]

Hi Paul,

If you really to format ALL empty cells on each worksheet, you can try the
following:

Sub Tester()
Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
With sh
If Intersect(.UsedRange, .Cells(Rows.Count, _
Columns.Count)) Is Nothing Then
.Cells(Rows.Count, Columns.Count).Font.Name = "Tahoma"
End If
On Error Resume Next
.Cells.SpecialCells(xlBlanks).Font.Name = "Tahoma"
On Error GoTo 0
End With
Next sh
End Sub

This is not likely to be excessively fast!

 

[Norman Jones]

Hi Swatsp0p,

I have no problem with the manual approach except that it is very
substantially slower than the programmatic solution. Using my procedure, I
was able to process a five sheet workbook in approximately the same time as
I was able to process a single sheet manually. This, of course, presupposes
the existence of the code. <g>



Incidentally, you could remove a redundant step from your suggestion:
instead of making an entry in cell IV65536 and later deleting the entry,
simply format it as Tahoma. No subsequent deletion is required and the
requisite used range is established.


That said, discussion as to the merits or demerits of one approach over
another is moot to the extent that the solution represents the resolution of
a problem that I would not expect to encounter.

 

[Tom Ogilvy]

I think you would want to delete the entire row and entire column of
IV65536 to reset the used range or your file size will grow tremendously -
which begs the question of why worry about these unused cells anyway. If
the Normal Style is set to Tahoma, then any "new" cell will be formatted
with Tahoma.

 

[Norman Jones]

Hi Tom,

I think you would want to delete the entire row and entire column of
IV65536 to reset the used range or your file size will grow tremendously

I found no need to delete either the row or column as I make no physical
entry in this cell. In testing, saving the file after running the procedure
was sufficient to restore the initial used range and, to within plus/minus
1%, the initial file size.

which begs the question of why worry about these unused cells anyway

I, myself, am not concerned by these cells and I endeavoured to convey this
in my final paragraph:

That said, discussion as to the merits or demerits of one approach over
another is moot to the extent that the solution represents the resolution
of
a problem that I would not expect to encounter.

As regards your final point:

If the Normal Style is set to Tahoma, then any "new" cell will be
formatted with Tahoma.

My understanding of the OP's requirement is that only blank cells be
formatted in Tahoma. I believe that changing the Normal Style acts on empty
and populated cells alike.

 

[Paul Black]

Hi,

Norman,
Your Macro Works and Changes ALL the Cells from A1 to IV65536 to
"Tahoma", Even those which have the Font of "Comic Sans MS" and
"Verdana", which I want Left as they are.

Tom,
When the Spreadsheet was First Created in 2002, the Normal Font was
Set to "Ariel". So ANY Input Automatically became "Ariel".
Once I have Managed to Achieve my Goal, the Spreadsheet will be given
back to the User, and then Any Additional Information Input ( Anywhere
in the Workbook ) will Automatically be in the Font "Tahoma".

Thanks also to Myrna Larson and swatsp0p for your Contributions.

All the Best
Paul

 

[Norman Jones]

Hi Paul,

Norman,
Your Macro Works and Changes ALL the Cells from A1 to IV65536 to
"Tahoma"

No. It operates uniquely on empty cells.

Even those which have the Font of "Comic Sans MS" and
"Verdana", which I want Left as they are.

Where did these conditions come from?!! This is rendered still more
perplexing when considered in juxtaposition to your response to an earlier
reply by Tom Ogilvy, in which you said you said:

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.

 

[Peter T]

Hi Norman and Paul

I agree Norman's macro should only change font in empty
cells. Don't see why it does not for Paul.

One thing though, if the "Normal" font is still set to
Arial, if a user subsequently clears formats the font will
revert to Arial.

I may have missed something in this thread but I don't see
what the problem is. From the opriginal post, all sheets
are like this:
Normal font: Arial
Formatted fonts: Tahoma and Comic

So, simply change the Normal font to Tahoma (Format >
Style). Comic Cells will remain Comic, Tahoma cells will
remain Tahoma, all unformatted cells will adopt the new
Normal/Tahoma (also row / col headers). Tom Ogilvy
suggested same.

But - any previous Normal/Arial cells that were formatted
say bold or a different font size will remain Arial. By
definition from the original post these could only be in
empty cells so not obvious. After changing the Normal
style, a macro could find and reformat all empty/non-
Tahoma cells in the Used range to Tahoma.

Regards,
Peter

 

[Tom Ogilvy]

You said:
I have Managed to Change the Column
Letters and Row Numbers from "Ariel" to "Tahoma" by using "Format",
"Style" and then Modifying the Font.


so any entry in a new, unused cell will be Tahoma.

 

[Norman Jones]

Hi Peter,

Re-reading Paul's original post in the light of your response, I realise
that I managed, inexcusably, to miss the important statement:

All the Cells in the Spreadsheet ( and Workbook ) that have
Information and Formulas in are Either "Tahoma" or "Comic Sans MS"
Font.

Taking this into account, Tom's advice to change the style to Tahoma has to
be the solution of choice whether the implementation be manual or
programmatic.

One thing though, if the "Normal" font is still set to
Arial, if a user subsequently clears formats the font will
revert to Arial.

As this does not appear to be something that the user could achieve
inadvertently, I doubt that it should cause the OP any anxiety.

 

[Peter T]

Hi Norman,

I realise that I managed, inexcusably, to miss the
important statement:

That's a bit self critical. I tend to congratulate myself
if I interpret something right way first time

Regards,
Peter

 

[Norman Jones]

Hi Peter,

Thank you - my sense of guilt is somewhat assuaged!

Thank you, also, for pointing out my initial oversight.

 

[Paul Black]

Thanks to Everyone who has tried to Solve my Problem, I think I will
have to do it the Long Handed Way.

so any entry in a new, unused cell will be Tahoma.

Tom, Unfortunately this is NOT the Case.

Even doing it the Long Handed Way, there are still going to be
Problems, like if they were to Insert a Column, that Column would
Automatically Default to the "Ariel" Font.
Not to Worry, the Task will still be Done, but just take much longer
than I anticipated.
Once again, Thanks to Everyone for their Contributions.

All the Best
Paul

 

[Tom Ogilvy]

so any entry in a new, unused cell will be Tahoma.
Tom, Unfortunately this is NOT the Case

We must be talking about differnt things. It certainly is true for me.