@ dflak
I got a syntax error at
msg = msg & vbTab & PadString,.Range("B" & i).Value,9) & vbTab & "CVRT Expiry" & vbTab & Format(.Range("l" & i).Value, "dd/mm/yyyy") & vbCrLf
@Bakerman2
Did the job after adding "+2 after your code, I can adapt that. Many thanks
Sub tst()
For Each cl In Range("B2", Range("B" & Rows.Count).End(xlUp))
mymax = Application.Max(mymax, Len(cl.Value) + 2)
Next
For Each cl In Range("B2", Range("B" & Rows.Count).End(xlUp))
msg = msg & vbTab & cl.Value & Space(mymax - Len(cl.Value)) & vbTab & "CVRT Expiry" & vbTab & Format(Date, "dd/mm/yyyy") & vbCrLf
Next
If msg <> "" Then MsgBox prompt:=Left(msg, Len(msg) - 1), Title:="CVRT Expiry Warning", Buttons:=vbExclamation
End Sub
Bookmarks