Blink or Flash Multiple Cells in Excel using VBA OnTime method

← PrevNext →

Excel is a very useful application for managing data on a spreadsheet. Its VBA tools and functions have made it more dynamic and appealing. Macros can make the contents in the file more meaningful and organized.

In this article we will discuss how multiple cells will blink (like changing colors) based on certain conditions. These blinking cells (with contents) 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 (that is 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.

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 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. Thanks for reading.

← PreviousNext →


7