Microsoft Excel - make only the word "shall" red

Asked By Dan on 27-Feb-12 07:15 AM
How can I make only the word "shall" red?  I am able the change all the text in every row to red that has the word shall in it, but I only want to change the word shall to red, and leave the rest of the text black on the worksheet.

Donald Ross replied to Dan on 27-Feb-12 09:23 AM
Hey Dan.

You can make the word Shall red by going back to the cells with teh text and then highlighting only the word shall up in the formula bar and change its properties that way. 

If you have hundreds to do you might be able to code it with a formula called Find that returns the starting place of text you specify in a text string.

wally eye replied to Dan on 27-Feb-12 12:15 PM
I did something similar a while back.  Open the VBA IDE (alt-F11), insert a new module and paste in this code:

Option Explicit
Public Sub btnSearch2_Click()
  Call SearchText2(Worksheets("Sheet2").Range("A3:A702"), _
End Sub
Public Sub SearchText2(ByVal lookup_array As Range, ByVal strSearch As String)
  Dim arrLookup     As Variant
  Dim lngRow      As Long
  Dim lngCol      As Long
  Dim intFoundPos   As Integer
  Dim intLenSearch    As Integer
  Application.EnableEvents = False
  Application.ScreenUpdating = False
  lookup_array.Font.Color = vbBlack
  lookup_array.Font.Italic = False
  lookup_array.Font.Bold = False
  If lookup_array.Cells.Count = 1 Then
    ReDim arrLookup(1, 1)
    arrLookup(1, 1) = lookup_array.Value
    arrLookup = lookup_array.Value
  End If
  intLenSearch = Len(strSearch)
  For lngRow = LBound(arrLookup) To UBound(arrLookup)
    For lngCol = LBound(arrLookup, 2) To UBound(arrLookup, 2)
      intFoundPos = InStr(1, arrLookup(lngRow, lngCol), strSearch)
      Do While intFoundPos > 0
        With lookup_array.Cells(lngRow, lngCol).Characters(intFoundPos, intLenSearch).Font
          .Color = vbRed
          .Italic = True
          .Bold = True
        End With
        intFoundPos = InStr(intFoundPos + 1, arrLookup(lngRow, lngCol), strSearch)
    Next lngCol
  Next lngRow
  Application.EnableEvents = True
  Application.ScreenUpdating = True
End Sub

Change the call to SearchText2 to match your worksheet, with the range to change being the first parameter, and the search value being the second parameter.  If you will always want to highlight "shall", you can just change it to read:

Call SearchText2(Worksheets("Sheet2").Range("A3:A702"), "shall")

Then, create a button on your worksheet to launch it (or just press F5 from within the btnSearch2_Click macro).

The code first resets the font color, bold and italics for the selected range, assign an array to the range's value, then loops through the range checking each cell for all occurences of the selected value.  When it finds the value, it changes the characters to vbRed, Italic and Bold.