Find sometimes ignores Application.FindFormat

  • Thread starter Thread starter Walter Briscoe
  • Start date Start date
W

Walter Briscoe

I use Excel 2003.
I have some data, which I intend to mark with a find, findnext loop.
I find that neither bold, nor italic marks work.
I have constructed some fairly simple code by editing a macro I
recorded.
I believe that find should not match a bold cell, but it does. ;(

This is my code, which is stand alone.
Option Explicit

Sub Macro1()
'
Workbooks.Add
Cells(1, 1).Value = "foo"
Cells(2, 1).Value = "fubar"
Cells(3, 1).Value = "fubar"
Cells(4, 1).Value = "foo"
Cells(2, 1).Font.Bold = True
Application.FindFormat.Clear
Application.FindFormat.NumberFormat = "General"
With Application.FindFormat
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Application.FindFormat.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Application.FindFormat.Borders(xlLeft).LineStyle = xlNone
Application.FindFormat.Borders(xlRight).LineStyle = xlNone
Application.FindFormat.Borders(xlTop).LineStyle = xlNone
Application.FindFormat.Borders(xlBottom).LineStyle = xlNone
Application.FindFormat.Borders(xlDiagonalDown).LineStyle = xlNone
Application.FindFormat.Borders(xlDiagonalUp).LineStyle = xlNone
Application.FindFormat.Interior.ColorIndex = xlNone
Application.FindFormat.Locked = True
Application.FindFormat.FormulaHidden = False
Cells(1, 1).Activate
Cells.Find(What:="fubar", LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=True).Activate
Debug.Print ActiveCell.Address & ", FoundStyle = " & _
ActiveCell.Font.FontStyle _
& ", DesiredStyle = " & Application.FindFormat.Font.FontStyle
End Sub

It writes
$A$2, FoundStyle = Bold, DesiredStyle = Regular
I don't understand why it does not write
$A$3, FoundStyle = Regular, DesiredStyle = Regular

I have not found any URL which is a good example of FindFormat.
I hope the mavens here can enlighten me.
Please?
 
I suspect the FindFormat feature only grabs stuff that's not 'regular'
formatting, or otherwise what's defined in 'Normal' Style! This is just
my assumption based on the behavior of your code example...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Hi Walter,

Am Fri, 4 Jul 2014 22:20:27 +0100 schrieb Walter Briscoe:
I use Excel 2003.
I have some data, which I intend to mark with a find, findnext loop.
I find that neither bold, nor italic marks work.
I have constructed some fairly simple code by editing a macro I
recorded.
I believe that find should not match a bold cell, but it does. ;(
Click to expand...

I had the same problem with xl2007 or later and I could manage it if I
create a cell e.g. Z1 with the expected formats and refer to that cell,
e.g.


With Application.FindFormat.Font
.Name = Range("Z1").Font.Name
.FontStyle = Range("Z1").Font.Fontstyle
.Size = Range("Z1").Font.Size
 
In message <[email protected]> of Sat, 5 Jul 2014 10:51:26
in microsoft.public.excel.programming, Claus Busch <claus_busch@t-
online.de> writes
Hi Walter,

Am Fri, 4 Jul 2014 22:20:27 +0100 schrieb Walter Briscoe:


I had the same problem with xl2007 or later and I could manage it if I
create a cell e.g. Z1 with the expected formats and refer to that cell,
e.g.


With Application.FindFormat.Font
.Name = Range("Z1").Font.Name
.FontStyle = Range("Z1").Font.Fontstyle
.Size = Range("Z1").Font.Size
.
.
.

End With
Click to expand...

Thanks Claus and also to Gary for shedding some light.

A quick check suggests the problem is cured in Excel 2010.
(I borrowed my son's machine. ;)

I find the following Property usage applies:
(I got the list of properties with
Dim P As CellFormat ' So locals window can see 2nd cell members
Set P = Application.FindFormat
)

Symbol Meaning
? Not checked
N Ignored in Application.FindFormat
Y Works in Application.FindFormat

With Application.FindFormat
.AddIndent ?
.Application ?
.Borders ?
.Creator ?
With .Font
.Application ?
.Background ?
.Bold N
.Color ?
.ColorIndex Y
.Creator ?
.FontStyle N
.Italic N
.Name Y
.OutlineFont ?
.Parent ?
.Shadow ?
.Size Y
.Strikethrough ?
.Subscript ?
.Superscript ?
.Underline N
End With
.FormulaHidden ?
.HorizontalAlignment ?
.IndentLevel ?
.Interior ?
.Locked ?
.MergeCells ?
.NumberFormat Y
.NumberFormatLocal ?
.Orientation ?
.Parent ?
.ShrinkToFit ?
.VerticalAlignment ?
.WrapText ?
End With
 
Back
Top