Microsoft Office Forums - View Single Post - [Solved] Help to Highlight a cell that has less than 5 characters

macropod · 11-07-2020, 02:19 PM

Yes, it's possible to test for specific strings. For example:

Code:

Sub Demo()
Application.ScreenUpdating = False
Dim oCell
For Each oCell In Selection.Tables(1).Range.Cells
  With oCell.Range
    If Split(.Text, vbCr)(0) = "Ltd." Then .HighlightColorIndex = wdYellow
    If Split(.Text, vbCr)(0) = "Inc." Then .HighlightColorIndex = wdYellow
  End With
Next
Application.ScreenUpdating = True
End Sub

but using Find/Replace would be more efficient:

Code:

Sub Demo()
Application.ScreenUpdating = False
Dim Rng As Range
With Selection.Tables(1)
  Set Rng = .Range
  With .Range
    With .Find
      .ClearFormatting
      .Replacement.ClearFormatting
      .Text = "[LI][tn][dc]."
      .Replacement.Text = ""
      .Forward = True
      .Wrap = wdFindStop
      .Format = False
      .MatchWildcards = True
    End With
    Do While .Find.Execute
      If .InRange(Rng) = False Then Exit Do
      With .Cells(1).Range
        If Split(.Text, vbCr)(0) = "Ltd." Then .HighlightColorIndex = wdYellow
        If Split(.Text, vbCr)(0) = "Inc." Then .HighlightColorIndex = wdYellow
      End With
      .Collapse wdCollapseEnd
    Loop
  End With
End With
Application.ScreenUpdating = True
End Sub