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
-----Original Message-----
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.
---
Regards,
Norman
"Paul Black" wrote in message
m...
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" wrote
in message
...
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.
---
Regards,
Norman
"swatsp0p" wrote
in message
news:EFC198AF-5AA6-49E6-9F60-
...
What happens if you put an entry in cell IV65536
then did:
Select a single cell | Hit the F5 function key |
Special | Check Blanks
|
OK
With the blank cells now selected, apply your
desired formatting.
Once formatted, delete the entry in IV65536?
"Norman Jones" wrote:
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!
---
Regards,
Norman
"Paul Black" wrote in
message
...
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
Myrna Larson
wrote in message
. ..
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
.