VBA code to automatically colour cells depending on text?
 

Hi, - with the skill of Don Guillet (Excel MVP - thank you!!) I have nearly
finished a €˜to-do list to help organise my life as an infant school teacher.
see attached link:

http://www.box.net/shared/ejzn141dux

The worksheet works like this:

Enter desired text in cell €˜c3, h3 or m3 and then use the drop down button
in €˜b3, g3 or l3 to determine the position where the text is to be placed in
the below list. If there is text already in that position, all the text below
is moved down one cell. If you delete text from a cell then the text in the
cells below moves up one. This part of it works fine.

The VBA code currently being been used is;

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range) 'SAS
Application.EnableEvents = False
Application.ScreenUpdating = False
If Not Intersect(Target, Range("B3,G3,L3")) Is Nothing Then

With Cells(Target + 5, Target.Column + 1)
If Len(Application.Trim(.Value)) < 1 Then
.Value = Target.Offset(, 1).Value
Else
Target.Offset(, 1).Copy
.Insert Shift:=xlDown
End If
End With
End If

Range("C6:C105,H6:G105,M6:L105").SpecialCells(xlCe llTypeBlanks).Delete
Shift:=xlUp
Application.CutCopyMode = False
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

I would like to know how to adapt the above code so that if I type a phrase
containing the word €˜Trago into cells c3, h3 or m3 the selected destination
cell would be highlighted light red?

I have tried conditional formatting but it does not retain after more than
one go €“ so I think the solution is adapting the above code?

Yours hopefully,
Matt.