January 13, 2016

How to make Excel Worksheet Cells Flash with VBA

We can make Excel worksheet cells flash with VBA so that the user is reminded of the action he is supposed to take. Because the process of flashing or blinking involves a certain type of animation we need to use a timer to accomplish the process. Watch the training video before diving into the VBA code:

Watch this video on YouTube.

The complete VBA code to animate cells data in Excel:

Module 1 formats the cells and calls the Timer function startTimer:

Sub makeCellsFlash()
Dim mydate1 As Date
Dim mydate2 As Long
Dim datetoday1 As Date
Dim datetoday2 As Long
Dim x As Long
lastrow = Sheets(“sheet1”).Cells(Rows.Count, 1).End(xlUp).Row
For x = 2 To lastrow
mydate1 = Cells(x, 2).Value
mydate2 = mydate1
datetoday1 = Date
datetoday2 = datetoday1
If mydate2 – datetoday2 = 3 Then
Cells(x, 3) = “Yes”
Cells(x, 3).Interior.ColorIndex = 3
Cells(x, 3).Font.ColorIndex = 2
Cells(x, 3).Font.Bold = True
End If
Next
startTimer
End Sub
Module 2 contains the VBA code for the timer:
Sub startTimer()
Application.OnTime Now + TimeValue(“00:00:01”), “color_cells”
End Sub
Sub color_cells()
With ThisWorkbook.Worksheets(“sheet1”).Range(“C2:C6”).Font
If .ColorIndex = 3 Then
.ColorIndex = 2
Else
.ColorIndex = 3
End If
End With
startTimer
End Sub
Sub endTimer()
Application.OnTime Now + TimeValue(“00:00:01”), “color_cells”, schedule:=False
End Sub
Further reading:

One thought on “How to make Excel Worksheet Cells Flash with VBA

  1. Dear Dinesh Kumar Takyar
    Thanks for quick replay
    But This is not what i hoped from the replay you send
    Because this is for flashing cells Not ActiveX Controls command Buttons as i wish
    i send mail For you with Attached Example for what i need in excel sheet
    hope to find the help from you with it
    Thanks and waiting for help´╗┐

Comments are closed.