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.