I want to create a worksheet with Staff dates of birth and get a notification
(column on worksheet blinking in any colour) 3 days to the staff's birthday
date whenever i put on my system

"sinecodap" wrote:

I want to create a worksheet with Staff dates of birth and get a notification
(column on worksheet blinking in any colour) 3 days to the staff's birthday
date whenever i put on my system

Need a solution to this please

On May 16, 10:40*am, sinecodap .(donotspam)
wrote:
I want to create a worksheet with Staff dates of birth and get a notification
(column on worksheet blinking in any colour) 3 days to the staff's birthday
date whenever i put on my system

A blinking column would require the use of a timer and would be a load
on your system. I would recommend going a different way. Maybe a
message box saying the employees name or something like that. A
simple For..Next statement could be used to loop through the employees
and determine if the birthday is within 3 days prior of the current
date.

Assuming the employee names begin in A2 and go down and their
associated birthday are in B2 and down, something like this would
work. Of course, it would probably be better to just display one
message box with all of the names listed.

Sub Auto_Open()
Dim i As Long
For i = 2 To Cells(65536, 1).End(xlUp).Row
If Date = (Cells(i, 2).Value - 3) And _
Date <= Cells(i, 2).Value Then
MsgBox Cells(i, 1).Value
End If
Next i
End Sub

Or, you can highlight the person row instead of displaying a message
box.
Sub Auto_Open()
Dim i As Long
Cells.Interior.ColorIndex = xlNone
For i = 2 To Cells(65536, 1).End(xlUp).Row
If Date = (Cells(i, 2).Value - 3) And _
Date <= Cells(i, 2).Value Then
Rows(i).EntireRow.Interior.ColorIndex = 40
End If
Next i
End Sub

I **hate** blinking text. Would you consider the alternative of simply
displaying a MessageBox showing you all the dates? Assuming the names are in
Column A, the birthdays in Column B, the data starting on Row 2 and the
worksheet name is Sheet3...

Assuming the names are in Column A and the birthdates are in Column B and
data starts on Row 2 and the data is on Sheet3...

Private Sub Workbook_Open()
Dim Dates As String
Dim CellDate As Date
Dim X As Long
Dim DaysDiff As Long
Dim LastRow As Long
Const StartRow As Long = 2
With Worksheets("Sheet3")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For X = StartRow To LastRow
CellDate = .Cells(X, "B").Value
DaysDiff = DateDiff("d", Date, DateSerial(Year(Now), _
Month(CellDate), Day(CellDate)))
If 0 < DaysDiff And DaysDiff <= 3 Then
Dates = Dates & .Cells(X, "A").Value & " - " & _
.Cells(X, "B").Value & vbCrLf
End If
Next
If Len(Dates) 0 Then MsgBox Dates
End With
End Sub


Rick



"sinecodap" .(donotspam) wrote in message
...
I want to create a worksheet with Staff dates of birth and get a
notification
(column on worksheet blinking in any colour) 3 days to the staff's
birthday
date whenever i put on my system