So I have a file where a task/person responsible etc. is listed
When the task is completed, I would like to enter yes and upon this
text entry, the file would delete that line and add it to another tab
with other completed tasks. I need other entries of text to have
nothing done.
Task Person Complete?
RFP Jane Doe
The below code kind of works but when anything other than "yes" is
entered in column C, the sheet event fails to activate. Help please:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Application.EnableEvents = False
If Intersect(Target, Range("c1:c100")) = "yes" Then
macrodef
'Turn events back on
Application.EnableEvents = True
'Allow run time errors again
On Error Resume Next
Else: MsgBox "Please leave blank if not complete"
End If
End Sub
Sub macrodef()
'
' macrodef Macro
Cells.Find(What:="yes", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
Rows(ActiveCell.Row).Select
Selection.Cut
Sheets("Completed").Select
Range("A2").Select
Selection.Insert Shift:=xlDown
Sheets("Active").Select
Selection.Delete Shift:=xlUp
MsgBox "Task was moved to completed tab"
End Sub
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 7000 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
