VBA for pop-up alert (listing names from two columns)

[charlie2503]

Hi everyone,

I'm very new to VBA and need some help coding a pop-up alert listing product names from 2 columns. The code that I'm using is displayed below. Is it possible to include another offset (i, 1, -2) in the formula to refer to another column? I also wonder if it's possible to make the pop-up display into 2 columns instead of 1 long list downwards? I don't understand how to add these into the formula. Your help would be very appreciared.

Private Sub Workbook_Open()

Dim RegNumberValidUntilCol As Range, RegNumberValidUntil As Range
Dim NotificationMsg As String, Prompts As String
Dim i As Long, a As Long, NoDays As Long
Dim ws As Worksheet

Prompts = "You do not have any Halal certificates expiring soon.," & _
"Halal certification of the following products are expiring soon:"

For Each ws In ThisWorkbook.Worksheets(Array("Sheet1"))

Set RegNumberValidUntilCol = ws.Range("M3:M3000,P3:P3000")

For i = 1 To RegNumberValidUntilCol.Areas.Count
For Each RegNumberValidUntil In RegNumberValidUntilCol.Areas(i)

If IsDate(RegNumberValidUntil) Then
NoDays = IIf(i < 3, RegNumberValidUntil - Date, Date - RegNumberValidUntil)
If NoDays >= IIf(i < 3, 0, 240) And NoDays <= 365 Then
If a = 0 Then NotificationMsg = NotificationMsg & Chr(10) & _
ws.Name & Chr(10): a = 1
NotificationMsg = NotificationMsg & _
RegNumberValidUntil.Offset(0, Choose(i, -3, -6)) & Chr(10)
End If
End If

Next RegNumberValidUntil
Next i
Set RegNumberValidUntilCol = Nothing
a = 0
Next ws

MsgBox Split(Prompts, ",")(IIf(Len(NotificationMsg) = 0, 0, 1)) & Chr(10) & _
NotificationMsg, 64, "Notifications"

End Sub