I have this macro that does what it is supposed to do, but with a couple of problems. I have a drop-down menu in "Prospect Status" column 14 and in column 15 "Date Status Change," it records the new status and the date to mark when a change is made. The dates collect, to read as so: "App Sent- 04/15/2016 App Received- 05/01/2016". That is great!
Here are the problems:
1. It deletes the information in the "Notes" cell in column 13.
2. I want to move the Prospect Status column to column A, but when I change the "Target, Column" from 14 to 1, it returns an error at line Target.Offset(, -1) = ""
I am new to macros, so I know I probably am just making a noobie mistake, but if someone could help me figure it out, I would be very grateful.
Here is the original macro:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Columns(14)) Is Nothing And Target.Count = 1 Then
If UCase(Target.Text) = "CLOSED" Then
Target.Offset(, -1) = Date
Target.Offset(, -3) = Date
Else
Target.Offset(, -1) = ""
Target.Offset(, -3) = ""
End If
If Target <> "" Then
Target.Offset(0, 1) = _
Target.Offset(0, 1) & Chr(10) & "-" & Format(Now(), "mm-dd-yyyy") & _
" " & Target.Text
Target.Offset(0, 1).EntireColumn.AutoFit
End If
End If
Application.EnableEvents = True
End Sub
Bookmarks