VBA Code to change color of field based on entry - query also.

[Craig & Co.]

Hi Everyone,

Here's some code that I found on the VBAExpress Forum, which changes
the formatting of the cell to
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Worksheet_Change(ByVal Target As Range)

Dim Cell As Range
Dim Rng1 As Range

On Error Resume Next
Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)
On Error GoTo 0
If Rng1 Is Nothing Then
Set Rng1 = Range(Target.Address)
Else
Set Rng1 = Union(Range(Target.Address), Rng1)
End If
For Each Cell In Rng1
Select Case Cell.Value
Case vbNullString
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
Case "Bob"
Cell.Interior.ColorIndex = 6
Cell.Font.Bold = True
Cell.Font.ColorIndex = 1
Case "Fred"
Cell.Interior.ColorIndex = 50
Cell.Font.Bold = True
Cell.Font.ColorIndex = 2
Case "Peter"
Cell.Interior.ColorIndex = 41
Cell.Font.Bold = True
Cell.Font.ColorIndex = 2
Case "Paul"
Cell.Interior.ColorIndex = 3
Cell.Font.Bold = True
Cell.Font.ColorIndex = 2
Case "Mary"
Cell.Interior.ColorIndex = 7
Cell.Font.Bold = True
Cell.Font.ColorIndex = 2
Case "David"
Cell.Interior.ColorIndex = 46
Cell.Font.Bold = True
Cell.Font.ColorIndex = 1
Case Else
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
End Select
Next

End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The code works a treat, however, my next problem is that the
next cell has a formula of "=E14", where E14 has the text of Bob.
The code above doesn't alter all the fields where Bob is found.

Any ideas on how to get the formatting to spead to all fields
where Bob appears?

Cheers
Craig.