Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Replace formatting (Office 2000)

    Hello,
    Is there any way to use find/replace to find text or a word in a sentence and replace the text with a format such as blue colored font or bold? It looks like find/replace only works to replace text?

    Thanks,
    Louise

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Replace formatting (Office 2000)

    No, you'd need to use a macro for that. Here is an example that makes the word "Weese" blue and bold:

    Sub FormatFound()
    ' Text to find
    Const strFind = "Weese"
    Dim rngCell As Range
    Dim strText As String
    Dim intPos As Integer

    If Selection Is Nothing Then Exit Sub

    On Error GoTo ErrorHandler

    Application.ScreenUpdating = False

    For Each rngCell In Selection.SpecialCells(xlCellTypeConstants).Cells
    strText = rngCell.Value
    intPos = InStr(strText, strFind)
    Do While intPos > 0
    With rngCell.Characters(intPos, Len(strFind)).Font
    ' Formatting to apply
    .Bold = True
    .Color = vbBlue
    End With
    intPos = InStr(intPos + 1, strText, strFind)
    Loop
    Next rngCell

    ExitHandler:
    Application.ScreenUpdating = True
    Exit Sub

    ErrorHandler:
    MsgBox "Something went wrong!", vbExclamation
    Resume ExitHandler
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace formatting (Office 2000)

    Hi Hans!
    Thank you. I'm guessing that I would have to edit the Macro each time I needed to search and replace a different word? Still would be faster than doing it manually!

    Thanks again!
    Louise

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Replace formatting (Office 2000)

    You could have the macro prompt for the text: replace the line

    Const strFind = "Weese"

    with

    Dim strFind As String
    strFind = InputBox("Enter the text to find.")

    Prompting for the desired formatting would be more difficult (but doable, if you really need it)

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace formatting (Office 2000)

    Great! Thank you!
    Louise

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •