Search For and Change Font For Certain Words With VBA

[4653]

I'm trying to find a VBA code that will search a particular range, for example A1:D300 on Sheet 1, for the phrase "Discounts Applied" and will increase the font to 16 and bold it. That particular phrase will be the only phrase in the cell it is located in. If it doesn't find that exact phrase then it does nothing. Basically what I have is a spreadsheet with a Print button and that phrase is a moving target from one day to the next so I was going to incorporate a code that will search for that phrase and put it in bold and 16 font in the same Print vba code so it does it just before it prints.

 

[Michael M]

Try

Sub MM1()
Dim c As Range
For Each c In Range("A1:D300")
    If c.Value = "Discounts Applied" Then
        With c.Font
            .Bold = True
            .Size = 16
        End With
    End If
Next c
End Sub

 

[Michael M]

This might be a little quicker

Sub MM1()
Dim rng As Range, rngLast As Range, rngFound As Range, strFirstAddress As String
Set rng = ActiveSheet.Range("A1:D300")
Set rngLast = rng.Cells(rng.Cells.Count)
Set rngFound = rng.Find(What:="Discounts Applied", After:=rngLast, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
If Not rngFound Is Nothing Then
strFirstAddress = rngFound.Address
Do
Set rngFound = rng.FindNext(rngFound)
 With rngFound.Font
    .Size = 16
    .Bold = True
    End With
Loop Until rngFound.Address = strFirstAddress
End If
End Sub

 

[Fluff]

Another option

Sub a4653()

   With Application.ReplaceFormat
      .Clear
      .Font.Size = 16
      .Font.Bold = True
   End With
   Range("A1:D300").Replace "Discounts Applied", "Discounts Applied", xlWhole, , False, , False, True
   Application.ReplaceFormat.Clear
End Sub

 

[4653]

Try

Sub MM1()
Dim c As Range
For Each c In Range("A1:D300")
    If c.Value = "Discounts Applied" Then
        With c.Font
            .Bold = True
            .Size = 16
        End With
    End If
Next c
End Sub

This worked perfect. Thanks so much. I haven't had a chance to try the other one out yet but I'll try it later this week and let you know if it accomplished what I was looking for also. Thanks again.

 

[4653]

Another option

Sub a4653()

   With Application.ReplaceFormat
      .Clear
      .Font.Size = 16
      .Font.Bold = True
   End With
   Range("A1:D300").Replace "Discounts Applied", "Discounts Applied", xlWhole, , False, , False, True
   Application.ReplaceFormat.Clear
End Sub

Thanks for the solution. However, it didn't work for me because my "Discounts Applied" was being pulled in by a formula. I tried your code and it only worked for me if the "Discounts Applied" was actually typed in a cell rather than being pulled in with a formula.