Blink or Flash Multiple Cells in Excel using VBA OnTime method

excel vba

← PrevNext →

Tools you can use

Excel is a very useful application for managing data on a spreadsheet. The tools, functions and properties that comes with VBA, makes it more useful when you want to automate a process. In this article we will discuss how multiple cells will blink (like changing colours) based on certain conditions. These blinking cells can serve as reminders and pull our attention on data that are important to our business.

Now let us say, I have two date columns. I wish to set an "alarm" (blink the cells) if the second date is "20" days from the "first" date.

See the below image 👇. Dates in the 1st and 4th rows are 20 days apart. Therefore, the second column cells will flash or blink every 1 second.

Note: I have mentioned the number of days (that is 20) in the forth column, with the heading Days to Calculate. You can set your number of days.

blinking multiple cells in excel

To make this trick work I'll use VBA’s Application.OnTime method, which allows me to set a timer that will call a procedure recursively.

Application.OnTime (EarliestTime, Procedure, LatestTime, Schedule)
The Macro

Press Alt+F11 keys to open VBA. From the VBA Projects Explorer choose ThisWorkBook. I want my Macro (the program) to compare the two dates immediately when the Excel file opens.

Private Sub Workbook_Open()
    alarm       ' CALL FUNCTION WHEN FILE OPENS.
End Sub

The Module

The real methods are in the Module section.

Note: See how I have a used isDate() function in the macro to check if the value in a cell is a date.

🚀 Do you know you can use a simple formula equivalent to isDate() function in Excel to check if value in a cell is a Date? Check this out.

Option Explicit

Dim dPeriod As Double           ' BLINK EVERY SECOND.
Dim arCellsToBlink()            ' ARRAY TO HOLD DATE VALUES.
Dim iCounter As Integer         ' JUST A COUNTER.

Dim iRows As Integer

Sub alarm()
    Dim iNoOfDays As Integer
    Dim iDaysToCompare As Integer
    
    iDaysToCompare = Range("Sheet1!D2")             ' NO. OF DAYS TO COMPARE.
    iRows = ActiveSheet.UsedRange.Rows.Count        ' GET THE ROWS USED IN THE SHEET.
    
    ReDim arCellsToBlink(0 To iRows)                ' REDIM THE ARRAY WITH THE MAX ROWS.
    Dim j As Integer
    
    For j = 4 To iRows
        If IsDate(Cells(j, 3)) Then         ' Check if value in the cell is a valid date.
            
            ' GET THE NO. OF DAYS BETWEEN DATE1 AND DATE2.
            iNoOfDays = DateDiff("d", Cells(j, 2), Cells(j, 3))
            
            Cells(j, 5) = iNoOfDays     ' SHOW THE DAYS IN COLUMN 5 (FOR REFERENCE).
            
            ' STORE THE ROWS IN AN ARRAY.
            If iNoOfDays = iDaysToCompare Then
                arCellsToBlink(iCounter) = "Sheet1!C" & j
                iCounter = iCounter + 1
            End If
        End If
    Next j
    
    Call FlashCell
End Sub

' THE PROCEDURE THAT WILL BE CALLED (RECUSIVELY).
Private Sub FlashCell()

    For iCounter = 0 To iRows
        If arCellsToBlink(iCounter) <> "" Then          ' THE MATCHING ROW(S).
        
            ' BLINK BY CHANGING THE CELL FONT AND BACK COLORS.
            If Range(arCellsToBlink(iCounter)).Interior.Color = vbRed Then
            
                Range(arCellsToBlink(iCounter)).Interior.Color = vbYellow
                Range(arCellsToBlink(iCounter)).Font.Color = vbBlack
                
            Else
                Range(arCellsToBlink(iCounter)).Interior.Color = vbRed
                Range(arCellsToBlink(iCounter)).Font.ColorIndex = 2
            End If
            
        End If
    Next iCounter

    dPeriod = Now + TimeSerial(0, 0, 1)
    
     ' THE OnTime METHOD.
    Application.OnTime dPeriod, "FlashCell", , True     ' CALL PROCEDURE "FlashCell".
End Sub

This method works automically when you open the Excel file. You can call the alarm method inside a button click event.

Well, that's it. 🙂

← PreviousNext →