SearchFormat And ReplaceFormat Please explain?

Question

I have looked and searched but I don't understand exactly what these do can someone explain? I wanted to add it as info in my module incase I needed to use it at some point. Here is what I wrote up so far and the last two I just don't know how to explain it. Thanks!

'***   7) SearchFormat:(Optional) Search cells according to a formatting criteria, like: background color, font color,

'***      font size, font style, etc....

'***         A. = True

'***         B. = False

'***

'***   8) ReplaceFormat:(Optional) The replace format for the method.

'***         A. = True

'***         B. = False

Answer 1

When you find or replace data, you can specify formatting. For example, let's say you want to find the word Test in column M, but only if the cell is bold.

Sub FindTest()

    Dim rngFound As Range

    ' Specify the format to find

    With Application.FindFormat

        .Font.Bold = True

    End With

    Set rngFound = Range("M:M").Find(What:="Test", _

        LookIn:=xlValues, LookAt:=xlPart, SearchFormat:=True)

    If Not rngFound Is Nothing Then

        MsgBox "Found in cell " & rngFound.Address, vbInformation

    Else

        MsgBox "Not found", vbInformation

    End If

End Sub

Similarly, if you want to apply formatting while replacing, specify ReplaceFormat.

The following example will replace the bold word Test with non-bold, red Testing.

Sub ReplaceTest()

    With Application.FindFormat

        .Font.Bold = True

    End With

    With Application.ReplaceFormat

        .Font.Bold = False

        .Font.Color = vbRed

    End With

    Range("M:M").Replace What:="Test", Replacement:="Testing", _

        LookAt:=xlPart, SearchFormat:=True, ReplaceFormat:=True

End Sub

Answer 2

Aha!  I wrote:

As I understood the question, it is:  what are all the possible values that can be assigned to SearchFormat?  (That is certainly my question.)

[....]

And then there is the potentially separate question regarding ReplaceFormat.  Again:  what are all the values that can be assigned, or how to we interpret its purpose if the values are simply True and False?

Apparently, the values for Find(SearchFormat) and Replace(ReplaceFormat) are indeed True and False.

And apparently, their interpretation is to indicate whether Find and Replace should use the Application.SearchFormat and Application.ReplaceFormat objects, which are set up separately.

See the CellFormat help page for some examples.

But for details, I find it helpful to use the Object Browser (press F2).  If we select the class Application, then the members ReplaceFormat and SearchFormat, we see that their type is CellFormat.  If we select the class CellFormat, we can see its members.  Et cetera.

---

@oxicottin:  Thanks for posting the question.  I have had the same question for a long time.  But I was never motivated enough to answer it for myself.

Answer 3

Thanks for posting this reply. So many years later, the description in Help for Range.Find and Range.Replace still lists the description of these variables as The search/replace format for the method; which certainly is an incorrect description if the values are True/False! In the early days of Microsoft, they put much more in the See Also section; nowadays, it seems to be limited to Methods/Properties or Parent objects. I understand the See Also could grow to unmanageable entries, but this is one case where a reference to CellFormat would be hugely helpful!

Answer 4

TasosK wrote:

what do you want..

Number Format, Fill, Font, Interior, Border, Bold ?

As I understood the question, it is:  what are all the possible values that can be assigned to SearchFormat?  (That is certainly my question.)

So if SearchFormat is intended to distinguish between number format, fill, font, interior border, bold, etc, how do we specify each criterion using SearchFormat?

A Google search reveals that some people think the values of SearchFormat are simply True and False.  If that is the case, how do we interpret the purpose of SearchFormat?

And then there is the potentially separate question regarding ReplaceFormat.  Again:  what are all the values that can be assigned, or how to we interpret its purpose if the values are simply True and False?