Re: Change Empty Cells Font for the Entire Workbook

From: Norman Jones (normanjones_at_whereforartthou.com)
Date: 09/11/04 

Date: Sat, 11 Sep 2004 18:03:05 +0100

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 Spread*** ( 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. 

---
Regards,
Norman
"Peter T" <peter_t@discussions.microsoft.com> wrote in message 
news:064c01c49819$58e985c0$a501280a@phx.gbl...
> 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" <paul_black27@hotmail.com> wrote in message
>>news:63f5e4.0409110103.6f28c32c@posting.google.com...
>>> 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 Spread*** 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 Spread***
> 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" <normanjones@whereforartthou.com> wrote
> in message
>>> news:<OztbTs1lEHA.952@TK2MSFTNGP14.phx.gbl>...
>>>> 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 *** workbook in
> approximately the same time
>>>> as
>>>> I was able to process a single *** 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" <swatsp0p@discussions.microsoft.com> wrote
> in message
>>>> news:EFC198AF-5AA6-49E6-9F60-
> 4BA56C55D626@microsoft.com...
>>>> > 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
> work***, you can try
>>>> >> the
>>>> >> following:
>>>> >>
>>>> >> Sub Tester()
>>>> >>     Dim sh As Work***
>>>> >>
>>>> >>     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" <paul_black27@hotmail.com> wrote in
> message
>>>> >>
> news:63f5e4.0409100216.7e5c8edc@posting.google.com...
>>>> >> > 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
> <anonymous@discussions.microsoft.com> wrote in message
>>>> >> >
> news:<b042k0t83omun9vdo1vqdge7l7qftsfn2d@4ax.com>...
>>>> >> >> 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_black27@hotmail.com (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" <twogilvy@msn.com> wrote in message
>>>>  news:<Op2kNZolEHA.3520@tk2msftngp13.phx.gbl>...
>>>> >> >> >> Sub Tester()
>>>> >> >> >>     Dim sh As Work***
>>>> >> >> >>
>>>> >> >> >>     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 ***
> that is formatted
>>>> >> >> >> the
>>>> >> >> >> way
>>>> >> >> >> you
>>>> >> >> >> want it.  click on the intersection and do
> Edit=>Copy, go to
>>>> >> >> >> this
>>>> >> >> >> ***,
>>>> >> >> >> 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" <paul_black27@hotmail.com> wrote
> in message
>>>> >> >> >>
> news:63f5e4.0409090733.566af999@posting.google.com...
>>>> >> >> >> > 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"
> <normanjones@whereforartthou.com> wrote in
>>>> >> >> >> > message
>>>>  news:<uaNdRwklEHA.1936@TK2MSFTNGP12.phx.gbl>...
>>>> >> >> >> > > 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 work***.
>>>> >> >> >> > >
>>>> >> >> >> > > Programmatically, try:
>>>> >> >> >> > >
>>>> >> >> >> > > Sub Tester()
>>>> >> >> >> > >     Dim sh As Work***
>>>> >> >> >> > >
>>>> >> >> >> > >     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" <paul_black27@hotmail.com>
> wrote in message
>>>> >> >> >> > >
> news:63f5e4.0409090012.4bb5326c@posting.google.com...
>>>> >> >> >> > > > Hi Everyone,
>>>> >> >> >> > > >
>>>> >> >> >> > > > I have been given a Spread*** which
> needs Updating.
>>>> >> >> >> > > > The Current ( Default on Excel for the
> Person that the
>>>> >> >> >> > > > ***
>>>> >> >> >> > > > 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 Spread*** ( 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
>>>> >> >> >> > > > Work*** 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
>>>> >>
>>>> >>
>>>> >>
>>
>>
>>.
>>
Quantcast