Can I set a Cell to Flash based on a particular DOW

Hi John,

I am posting my solution as an answer so you can select it.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' when cell C3 is selected the message box pops-up
If Target.Address = "$C$3" Then
    MsgBox "Your reminder goes here."
End If
End Sub

Here is a repeat of the other option I suggested.

Another option, provided there is space available say an empty row, is to have an unused cell provide the reminder. This would require an in-cell formula and conditional formatting. The in-cell formula would check to see if the current day is Thursday, if so enter the reminder message, if not leave it blank. The CF rule would add cell fill colour if the cell contains text. This method would cause the reminder to be visible all day Thursday without affecting the functionality of the sheet/workbook.

Cheers   :-)

Hi JohnThro60

In the attached spreadsheet, I've created a more general task list on Sheet1, with any repeat day currently in column B- feel free to change them. Any number of rows might be used.

When the file opened for the first time on a day, say a Saturday, all tasks due on that weekday will have N set in the Done column and the row will flash yellow every second (for a defined number of times) then become yellow. I've done that since constantly flashing would be just irritating in my opinion.

If it's opened again on the day, only those rows with N in the Done? column will flash. That's achieved using the worksheet Last Used (which could be hidden or VBA VeryHidden) which is used to record the date it was last opened. (You can manually  set it to a previous day to test the feature above). That's done when the file is opened, using this (commented) code:

Private Sub Workbook_Open()

    ' stop triggering other coes
    Application.EnableEvents = False
    ' if file first opened today, call procedure
    If Date > Sheet2.Cells(1, 2).Value Then Call DayDue

    ' record date of opening in hidden sheet
    Sheet2.Cells(1, 2) = Date

    Application.EnableEvents = True
    'initiate flashes
    Call Timer

End Sub

where the first bold lines set today's rows to yellow if needed. After that (and recording the date), the second bold ones call the timer routine (in Module 1).

Module 1 starts with some Public declarations (with hopefully meaningful names if you look at Sheet1): for constants (which you can alter) and variables (in bold for the Timer)

' define where the data is
Public Const DayClm As Long = 2, DoneClm As Long = 5, DataStartRw As Long = 4, DataLastClm As Long = 8, DoneTimeClm As Long = 6
' define variable for flash timer
Public Const MaxFlashes As Long = 7
Public Interval As Double, Flashes As Long

The code which clear tasks (and sets them to yellow) on first opening on a given day is this:

Sub DayDue()
    ' initiated on first workbook opening of the day

    Dim Rw As Long, LastDayRw As Long

    ' find last row containing a Repeat day
    LastDayRw = Sheet1.Cells(Rows.Count, DayClm).End(xlUp).Row
    ' loop down
    For Rw = DataStartRw To LastDayRw
        With Sheet1.Cells(Rw, 1)
            ' check if due today
            If .Offset(0, DayClm - 1).Value = Format(Date, "dddd") Then
                ' if so, colour row, set Done to N and clear completion columns
                .Resize(1, DataLastClm).Interior.Color = vbYellow
                .Offset(0, DoneClm - 1).Value = "N"
                .Offset(0, DoneClm).Resize(1, 3).Value = ""
            End If
        End With
    Next Rw

End Sub

The Workbook_Open code initiates the code which flashes the rows. This is based on one of Don's Tutorials (in that section) : Run a Macro at Set Intervals in Excel but I've counted the Flashes and stoppped them after the defined MaxFlashes (main changes in bold):

Sub Timer()

    If Flashes <= 2 * MaxFlashes - 1 Then
        ' add 1 second
        Interval = Now + TimeValue("00:00:01")
        'Tell Excel when to next run the macro.
        Application.OnTime Interval, "Flash"

        Else
        ' reset count for next open
        Flashes = 0
    End If

End Sub

Sub Flash()

    Dim Rw As Long, LastDayRw As Long

    ' find last row containing a Repeat day
    LastDayRw = Sheet1.Cells(Rows.Count, DayClm).End(xlUp).Row
    ' loop down
    For Rw = DataStartRw To LastDayRw
        With Sheet1.Cells(Rw, DoneClm)
            ' check if Done is N
            If .Value = "N" Then
                ' if so, toggle row colour
                If .Interior.Color = vbYellow Then
                    Sheet1.Cells(Rw, 1).Resize(1, DataLastClm).Interior.ColorIndex = xlNone
                    Else
                    Sheet1.Cells(Rw, 1).Resize(1, DataLastClm).Interior.Color = vbYellow
                End If
            End If
        End With
    Next Rw

    ' count flash
    Flashes = Flashes + 1
    'Calls the timer macro so it can be run again at the next interval.
    Call Timer

End Sub

The timer is stopped after MaxFlashes via:

Sub StopTimer()
    On Error Resume Next
    'stop flash due
    Application.OnTime earliesttime:=Interval, procedure:="Flash", schedule:=False

End Sub

which is also called when the workbook closes (to prevent any flash events hanging around).

Furthermore, There's data validation for the days (in column B) and Done? in column E (Y/N/blank)- if you change the latter to Y, the event macro will clear the highlight (if yellow, including during flashes) and record the time in column F. If you need to change it back to N, it will restore the yellow and clear that time.. That's done by this event macro (behind Sheet1):

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rw As Range

    ' do nothing unless single cell in Done column is changed
    If Intersect(Target, Columns(DoneClm)) Is Nothing Or Target.CountLarge > 1 Then Exit Sub
    ' stop retriggering this event code
    Application.EnableEvents = False
    ' check value and if Y add time and clear fill
    If Cells(Target.Row, DoneClm) = "Y" Then
        Cells(Target.Row, DoneTimeClm) = Now
        Range(Cells(Target.Row, 1), Cells(Target.Row, DataLastClm)).Interior.ColorIndex = vbnone

        Else 'reapply colour
        Cells(Target.Row, DoneTimeClm) = ""
        Range(Cells(Target.Row, 1), Cells(Target.Row, DataLastClm)).Interior.Color = vbYellow
    End If
    Application.EnableEvents = True

End Sub

Hope this fixes things for you or gives you a good clue for your particular issue. If so, please be sure to mark this Answer as Selected.