Expiry Notification System VBA Code - Excel VBA

Submitted by CAAadmin on Sat, 03/09/2019 - 07:55

CREATE AN EXPIRY DATE NOTIFICATION SYSTEM IN EXCEL

Steps

1: Open a new excel work book

2: Arrange Titles

3: Insert Button

4: Add VBA codes

5: List all products/items expiry

6: Click Notification button

7: See the Colour changes (Expired items)

 

CODE

 

 

Dim i As Integer
Dim LDate As Date
Dim Lvalue As Date
For i = 5 To 500

ActiveSheet.Range("F" & i).Interior.ColorIndex = 0

Next


LDate = Date
Lvalue = Format(LDate, "dd/mm/yyyy")


For i = 5 To 500

If ActiveSheet.Range("F" & i) <> "" Or ActiveSheet.Range("F" & i) <> 0 Then


If Format(ActiveSheet.Range("F" & i), "dd/mm/yyyy") < LDate Then
'If ActiveSheet.Range("F" & i) < LDate Then

ActiveSheet.Range("F" & i).Interior.ColorIndex = 3
ElseIf ActiveSheet.Range("F" & i) > LDate Then
ActiveSheet.Range("F" & i).Interior.ColorIndex = 0

End If
End If

Next

Image
Expiry Notification System VBA Code - Excel VBA