This answer is provided by Tushar Mehta

ID: 14

Category: Excel Dynamic Formatting

Question: How can I make a cell (or some other object) flash?


Dynamic formatting, such as a flashing cell, is not natively possible with Excel. However, one can implement it with a simple macro and conditional formatting. This combination allows one to create different 'flash' effects for different cells. In addition, formatting changes do not require any knowledge of VBA programming. The simple macro provides a timer -- a metronome with a one second beat

For any cell that you want to 'flash' put in a conditional formatting condition (using Formula Is) of =MOD(SECOND(NOW()),2)=1. Set the font/pattern/whatever as desired.

Now, in a regular VBA module, add the following:

Option Explicit

Dim NextTime As Date
Sub RepeatOneSec()
    ActiveWorkbook.Styles("normal").NumberFormat = _
    NextTime = Now() + TimeSerial(0, 0, 1)
    Application.OnTime NextTime, "RepeatOneSec"
    End Sub
Sub EndProcess()
    Application.OnTime NextTime, "RepeatOneSec", , False
    End Sub

Run the RepeatOneSec macro.

Use the EndProcess macro to stop the flashing effect.

Note that for some special effects you may need to use

instead of

    ActiveWorkbook.Styles("normal").NumberFormat = _

The reason I like this metronome solution is that by removing all the content and format control from the VBA code, it leads to a magnitude increase in flexibility.  One could have some cells change format on a two second cycle, others on a three second cycle.  The change in format could be a 'blinking' effect, or a border on-off effect or whatever one's imagination can come up with.

Go crazy and have one set of cells alternate between red and white, while another set goes green or blue.  That will give the user a nice fat headache rather quickly.

Or, get more A1:A10 enter an asterisk (SHIFT + 8).  Format the cells so that the font is White (strictly speaking, it should be the same as the background).  Now, add one conditional format.  Use Formula Is and set the formula to =(MOD(SECOND(NOW())-1,10)+1)=ROW().  Set the associated format to a font color = Red.  Turn on the timer, and it will look like the asterisk moves down from row 1 to row 10, one step each second, and then restarts the cycle in row 1.  [Instead of the asterisk, use a right arrow alt+16 in the Arial font]

Dynamic formatting of a chart point

Suppose there is a data set in B1:B10 that is plotted in a XY Scatter chart as a line with no markers.  The value in B2 is to be emphasized in the chart with a blinking point.  In D2 enter the formula =IF((MOD(SECOND(NOW())-1,2))=1,B2,NA()).  Add this data point to the chart (as a new series).  Use the metronome subroutine (RepeatOneSec) with the Application.Calculate version


Website material copyright 2003-2006 TM Faculty Associates