Im in a VB mess

[pickwa]

Hi Guys,

I have a speadsheet that is going to tell me when to send out letters to people.

i am trying to create a status box

when a date is entered into a cell(date of letter sent) the status box says one thing. eg 'letter sent'

then(this is where i got stuck) one month later i want the status box to automatically change to somthing else "pass on"

i currently have it so it changes 30 years after i think????

i really am clueless when it comes to VB and this is litterally the last thing i need to complete my spreadsheet.

if anyone could show me how to change my script it would be massively apprecaited because im completely stumped/clueless

cheers,

Pickwa

ive included all of my script below it make make it clearer what i am trying to achieve.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub
If Target.Column = 10 Then
If Target.Value > 0 Then Target.Offset(0, -1).Value = "processing"
End If
If Target.Column = 11 Then
If Target.Value = "No" Then Target.Offset(0, -2).Value = "Send 1st Letter"
If Target.Value = "Yes" Then Target.Offset(0, -2).Value = "Case Closed"
End If
If Target.Column = 12 Then
If Target.Value <> "" Then Target.Offset(0, -3).Value = "1st letter sent"
If Target.Value > Date - 30 Then Target.Offset(0, -3).Value = "send Charge Letter"
End If
If Target.Column = 13 Then
If Target.Value <> "" Then Target.Offset(0, -4).Value = "Charge Letter Sent"
If Target.Value > Date - 30 Then Target.Offset(0, -4).Value = "Send Surcharge Leter"
End If
If Target.Column = 14 Then
If Target.Value > "0" Then Target.Offset(0, -5).Value = "Surcharge Letter Sent"
If Target.Value > Date - 30 Then Target.Offset(0, -5).Value = "Pass On"
End If
If Target.Column = 15 Then
If Target.Value >= "0" Then Target.Offset(0, -6).Value = "Case Closed"
End If
If Target.Column = 17 Then
If Target.Value = "YES" Then Target.Offset(0, -6).Value = "Paid"
End If
End Sub <!-- / message -->

 

[Comfy]

Private Sub Worksheet_Activate()
Application.EnableEvents = False
Dim rng As Range
Set rng = Range("N1:N" & Range("N65536").End(xlUp).Row)
    For Each cell In rng.Cells
        If Not cell.Value = "" Then
            If Date > cell.Value + 30 Then cell.Offset(0, -5).Value = "Pass On"
        End If
    Next
Application.EnableEvents = True
End Sub

On sheet activation the comment will change if the cell value is older than one month. You can change the triggering event. Possibly Workbook_Open

 

[pickwa]

Sorry to sound stupid but where would i put that in the script?

thanks for helping me you are a lifesaver!

 

[Comfy]

You dont need to paste this in your current script.

Its another Sub that you can paste before or after you current one.

Would look like this:

Private Sub Worksheet_Activate()
Application.EnableEvents = False
Dim rng As Range
Set rng = Range("N1:N" & Range("N65536").End(xlUp).Row)
    For Each cell In rng.Cells
        If Not cell.Value = "" Then
            If Date > cell.Value + 30 Then cell.Offset(0, -5).Value = "Pass On"
        End If
    Next
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub
If Target.Column = 10 Then
If Target.Value > 0 Then Target.Offset(0, -1).Value = "processing"
End If
If Target.Column = 11 Then
If Target.Value = "No" Then Target.Offset(0, -2).Value = "Send 1st Letter"
If Target.Value = "Yes" Then Target.Offset(0, -2).Value = "Case Closed"
End If
If Target.Column = 12 Then
If Target.Value <> "" Then Target.Offset(0, -3).Value = "1st letter sent"
If Target.Value > Date - 30 Then Target.Offset(0, -3).Value = "send Charge Letter"
End If
If Target.Column = 13 Then
If Target.Value <> "" Then Target.Offset(0, -4).Value = "Charge Letter Sent"
If Target.Value > Date - 30 Then Target.Offset(0, -4).Value = "Send Surcharge Leter"
End If
If Target.Column = 14 Then
If Target.Value > "0" Then Target.Offset(0, -5).Value = "Surcharge Letter Sent"
If Target.Value > Date - 30 Then Target.Offset(0, -5).Value = "Pass On"
End If
If Target.Column = 15 Then
If Target.Value >= "0" Then Target.Offset(0, -6).Value = "Case Closed"
End If
If Target.Column = 17 Then
If Target.Value = "YES" Then Target.Offset(0, -6).Value = "Paid"
End If
End Sub

 

[pickwa]

Hi sorry i didnt realised you had replied thanks again bro!

Thanks for your help its much appreciated!