Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Flashing Cells.

Flashing Cells

by Allen Wyatt
(last updated December 17, 2015)

24

Many people use the conditional formatting features of Excel to draw attention to specific values or areas of their worksheets. For instance, a cell might be formatted so that its contents are displayed in red or in boldface if above or below a certain threshold.

What is missing, however, is a way to make the contents of a cell flash, or blink on and off. For such a feat, you are left to your own devices and the miracle of macros. By utilizing these tools, you can make cells blink by first designing a special style for the blinking cells, and then running a simple macro.

To create the special style, follow these steps:

  1. Select the cell that you want to flash on and off.
  2. Choose Style from the Format menu. Excel displays the Style dialog box. (See Figure 1.)
  3. Figure 1. The Style dialog box.

  4. In the Style Name box, enter a new style name. (For this example, use the style name Flashing.)
  5. Using the controls in the dialog box, modify any attributes for the style, as you desire.
  6. Click on OK.

You can now apply the style to any other cells you desire in your workbook. Now create the macros (there are two of them), as follows:

Dim NextTime As Date

Sub StartFlash()
    NextTime = Now + TimeValue("00:00:01")
    With ActiveWorkbook.Styles("Flashing").Font
        If .ColorIndex = xlAutomatic Then .ColorIndex = 3
        .ColorIndex = 5 - .ColorIndex
    End With
    Application.OnTime NextTime, "StartFlash"
End Sub

Sub StopFlash()
    Application.OnTime NextTime, "StartFlash", schedule:=False
    ActiveWorkbook.Styles("Flashing").Font.ColorIndex = xlAutomatic
End Sub

To start the items flashing, simply run StartFlash. The cells formatted with the Flashing style will alternate between red and white text approximately once a second. When you want to turn the flashing off, simply run the StopFlash macro.

There is one important thing to note about this macro: the variable NextTime is declared outside of the actual procedure in which it is used. This is done so that NextTime maintains its value from one invocation of StartFlash to the next.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2134) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Flashing Cells.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Inserting Datasheet Rows or Columns

Microsoft Graph provides a handy way to add simple charts to your document without the need for Excel. Those charts are based ...

Discover More

Calculated Dates

Word makes it easy to insert today's date in a document, but not as easy to insert a date X number of days in the future. ...

Discover More

Combining Word Documents

At some point you may want to insert one Word document inside another Word document. An easy way to do this is to use the ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (menu)

No More Custom Formats Can Be Added

If you make too many formatting changes to your workbook, you could end up with a situation where you cannot make any such ...

Discover More

Conditionally Formatting an Entire Row

Need to conditionally highlight an entire row based on the contents of a single cell in each row? This tip explains how you ...

Discover More

Controlling How Excel Interprets Percentages

When entering data in a worksheet, Excel tries to figure out how your entry can best be shown on the screen. When it comes to ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is two minus 2?

2016-12-11 00:47:28

Peter

Hi Allen, I have been trying for many months to achieve the flashing cell. What I am trying to do is make a cell flash but have a point that I maybe able to adjust the speed of the flashing. I am a drummer and I am trying to get a metronome try of effect. I have even tried to insert a metronome into excel but failed badly. Can you help or point me in the right direction. I am running Windows 10 and Office 2007, thanks, Peter


2016-11-16 05:45:27

Barry

@huruy

If you use the macro code I published here on 31 July 2015, then you can use this as an additional condition to that you are using to turn a cell red or green. BUT you will need to enter the condition as a formula.

So let's say you want cell A1 to turn green if its value is over 50 and turn red is it is less than 25. In the conditional formatting dialog box select "Formula is" instead of "Cell Value is" then enter the formula =AND(Flash=0,A1<25) and set the format to Red, and add a second condition in a similar manner but use the formula =AND(Flash=0, A1>50) and set the format to Green.

You can load a test workbook from here: http://bit.ly/24thJnc it is in the newer format (.xlsm) but you can open it if you've installed the fileconvertor plug-in, you'll also need to check your security settings to allow the macros to run. This flashes cell F10 if its value is 5 or above.



2016-11-15 09:49:19

huruy

hi i have an excel data which can vary from day to day based on my entry data but i need to flash the reporting data using red color if data is below target and green color if data is above target so how can i do this its not time dependent it value dependent.


2016-10-14 05:03:00

durga prasad

good one


2016-10-02 05:59:38

Barry

@Louis

The problem with this code is VBA will not allow a value of .ColorIndex to be negative so if you subtract the current ColorIndex value from 5 and it results in a negative number then you will get a runtime error.

You need to change the code to ensure that this cannot occur, or use an alternative method to flash cells (such as I suggested on 31 Jul 2015).


2016-10-01 12:32:30

Louis LAFRUIT

The vba compiler doesn't accept
.ColorIndex = 5 - .ColorIndex
***
With ActiveWorkbook.Styles("Flashing").Font
If .ColorIndex = xlAutomatic Then .ColorIndex = 3
.ColorIndex = 5 - .ColorIndex
End With
***
For shich reason ?


2016-07-21 05:23:39

JAGDISH R PATEL

I used Excel cell flashing. But its not starting flashing at all in M S Excel 2010.


2016-05-27 04:45:23

parvathi nair

Excellent.i have done it


2016-05-03 22:16:57

Zack

Can I change the interior color instead font color? How do I do that?


2015-09-28 15:21:32

Zak

Can someone create a youtube video with each steps to create a flashing cell. Not experienced in excel


2015-08-04 14:58:02

Kadr Leyn

I made a template about flashing cell.

https://www.youtube.com/watch?v=LoLoboLTeHk


2015-07-31 05:54:26

Barry

@Paul,

I have a spreadsheet that does exactly this.

I use a very simple macro that toggles the value of a "Name" once per second. Then I use conditional formatting to either highlight the target cell or not dependent on the value in the Name.
NB a Name doesn't have to refer to a Named range which it is most commonly used for but can hold variables, or even formulas.

In a code "Module":

Public HighlightTime As Date

Sub RefreshHighlight()

If ThisWorkbook.Names("Flash") = "=1" Then
ThisWorkbook.Names("Flash").Value = "=0"
Else
ThisWorkbook.Names("Flash").Value = "=1"
End If

HighlightTime = Now + TimeValue("00:00:01") 'SetTime for +1 secs
Application.OnTime HighlightTime, "RefreshHighLight"
End Sub

and on the "ThisWorkbook" codepage:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime HighlightTime, "RefreshHighLight", , False
End Sub

Private Sub Workbook_Open()
ThisWorkbook.Names.Add "Flash","0"
RefreshHighlight
End Sub

Then in any cell you want to flash use the conditional formatting forumla: "=Flash=1" and whatever formatting you desire.

NOTE: the placement of the macro on the specified codepages/modules it important.
and the use of these macros clears the "Undo" stack.


2015-07-30 08:38:27

Paul

Is it possible to highlight a cell say in yellow and just have the highlighted colour flash on and off. The contents i.e. text or numbers of the cell should stay the same.


2015-05-18 19:45:55

Allen

Very handy! I use it inside a stopwatch macro for tracking billable hours while I work.

Instead of changing font colour, though, it may be easier just to toggle bold:

With ActiveWorkbook.Styles("Flashing").Font
.Bold = Not .Bold
End With


2015-01-20 14:16:50

Glenda D

Just a tip for others, DON'T use black as the base color. The macro is subtacting to get the alternate color. And BLACK is 1.

Styles format menu is accessed from HOME ribbon, Styles box, Cell Styles drop down.


2014-12-17 07:04:06

BHAVESH

This is bhavesh form ahmedabad, India. I had trying this above mentioned code but it given error.

please give me idea



2014-11-05 09:49:42

awyatt

Roger,

This page is for users of older versions of Excel, as noted at both the beginning and end of the tip. Also in those places (beginning and end) is a link to a tip for later versions of Excel, including yours.

-Allen


2014-11-05 09:34:22

Roger

Hello, I'm using Excel 2010 but I cannot find the Format menu that you mention. I do have a Format Cells menu but that does not have a tab for Styles. Can you please advise.

Thank you


2014-08-30 23:59:51

Tim

Can you show us how to associate new style "Flashing" to the new created macro using VB?


2014-07-15 08:29:12

vinit kumar jha

Hi,

I am vinit and wanted to use Flash option in a cell...already created module as per above mentioned VB code...

but getting " Run time error 9)

Script out of range..

.ColorIndex = 5 - .ColorIndex


2013-10-17 11:20:25

John Morgan

I am trying to use the Flashing Cells Tip.
It is giving me a compilation error
"unable to set the ColorIndex property of the Font class".
The debugger highlights the code
".ColorIndex = 5 - .ColorIndex"

I am using 2002 Excel.
Can you help me please.

John.


2013-09-17 06:34:33

Asif Kamal

Excellent one...great


2013-07-05 07:57:44

Dave

Is it possible to make the cell flash if the date is reaching close to 30 days. I want to be notified that a client has not called within the 30 days period so that when it starts to flash I can call them.


2013-06-01 06:21:14

Barry Fitzpatrick

It is some times easier for Users simply to have the macro toggle a cell value between TRUE and FALSE and use conditional formatting to flash whatever formatting the user desires without the need to delve into the macro itself and the to have knowledge of properties of the of cells and restrictions on the value they can be set to.


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.