I only have basic knowledge in advanced excel and zero knowledge of vba and macro.
In one sheet of the workbook i have a list of employees and their visa, id and passport expiration. then i made a column that calculates it's remaining days before expiration. i made some researched and tried creating a code but the alerts only show when the excel workbook is opened and the macro runs in the entire workbook which is driving me crazy=) please help me.
here is my worksheet (up to 53rd rows)
B= Name
C= Id
D= id expiry
E= Visa
F= visa expiry
G= passport
H= passport expiry
I= remaining days of id and visa
J= remaining days of passport
K= today's date
Here is my code:
Sub Auto_open()
Dim r As Long
For r = 2 To 53
If Range("I" & r).Value <= 0 And Range("K2").Value >= Range("I" & r).Value Then
MsgBox Range("B" & r).Value & " VISA/EID has Expired"
End If
Next r
For r = 2 To 53
If Range("I" & r).Value <= 30 And Range("K2").Value >= Range("I" & r).Value Then
MsgBox Range("B" & r).Value & " VISA/EID almost Expiring"
End If
Next r
For r = 2 To 53
If Range("J" & r).Value <= 0 And Range("K2").Value >= Range("J" & r).Value Then
MsgBox Range("B" & r).Value & " PASSPORT has expired"
End If
Next r
For r = 2 To 53
If Range("J" & r).Value <= 0 And Range("K2").Value >= Range("J" & r).Value Then
MsgBox Range("B" & r).Value & " PASSPORT almost expiring"
End If
Next r
End Sub
