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

Flashing Cells

by Allen Wyatt
(last updated January 12, 2018)

51

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. (Make sure the cell has something in it; it should not be blank.)
  2. Make sure the Home tab is displayed on the ribbon.
  3. In the Styles group, click Cell Styles. Excel displays a drop-down selections of pre-defined styles.
  4. Choose New Cell Style. Excel displays the Style dialog box. (See Figure 1.)
  5. Figure 1. The Style dialog box.

  6. In the Style Name box enter the name "Flashing" (without the quote marks).
  7. Using the controls in the dialog box, modify any attributes for the style, as you desire.
  8. 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.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7223) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel 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

Selecting Sentences

Need to select an entire sentence at once? You can do so by creating a short macro that does the task for you, or you can ...

Discover More

Deleting Index Entries

When you construct an index you need to insert all sorts of index fields throughout your document. If you want to later ...

Discover More

Discovering Printer Drift

How accurate is your printer when it comes to placing information on the printed page? The simple technique described in ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Printing a Multi-Line Footer

Add a footer to your document and you may want to make it span more than a single line. This can be easy to do, provided ...

Discover More

Indenting Cell Contents

Excel allows you to apply several types of alignments to cells. One type of alignment allows you to indent cell contents ...

Discover More

Displaying Negative Times

Excel allows you to perform math using times as operands. If you subtract a later time from an earlier time, you should ...

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. Maximum image size is 6Mpixels. 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 4 + 7?

2019-10-19 15:50:54

J. Woolley

@JD
You can read the formatted version of my previous comment on Google Drive here:
https://drive.google.com/open?id=1d8oY_It5TxsxwYIQ79sT6luDPb65GHKT


2019-10-19 15:30:34

J. Woolley

Sorry the format of my previous comment got so screwed up. It didn't look like that when I submitted it.


2019-10-19 14:14:26

J. Woolley

@JDThe following VBA code will flash the ActiveCell for a specific WorkSheet:1. Press Alt+F11 to open the Visual Basic Editor (VBE).2. Under VBAProject > Microsoft Excel Objects, click the applicable sheet and press F7 to open the Code pane, then paste the following VBA code (which applies only to the selected worksheet):Private Sub Worksheet_Activate()FlashInitializeFlashStartEnd SubPrivate Sub Worksheet_Deactivate()FlashStopEnd SubPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)FlashStopFlashInitializeFlashStartEnd Sub3. Under VBAProject > Microsoft Excel Objects, click ThisWorkbook and press F7 to open the Code pane, then paste the following VBA code:Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)FlashStopEnd Sub4. From the VBE menu, pick Insert > Module, then paste the following VBA code for this new Module (which must be in the same VBAProject):Dim LastCell As Range, LastColor As Long, NextTime As DateSub FlashInitialize() Set LastCell = ActiveCell With ActiveCell.Interior If .ColorIndex = xlColorIndexNone Then LastColor = .ColorIndex Else LastColor = .Color End If End WithEnd SubSub FlashStart() NextTime = Now + TimeValue("00:00:01") With ActiveCell.Interior If .ColorIndex = xlColorIndexNone Then .ColorIndex = 6 Else .ColorIndex = xlColorIndexNone End If End With Application.OnTime NextTime, "FlashStart"End SubSub FlashStop()On Error GoTo Done Application.OnTime NextTime, "FlashStart", , False With LastCell.Interior If LastColor = xlColorIndexNone Then .ColorIndex = LastColor Else .Color = LastColor End If End WithDone:End SubNow the fill color of the ActiveCell in the applicable worksheet will flash between Yellow and None. If you want to apply this to all sheets in the workbook, modify step 2 (only) as follows:2. Under VBAProject > Microsoft Excel Objects, click ThisWorkbook and press F7 to open the Code pane, then paste the following VBA code (which applies to all sheets in the workbook):Private Sub Workbook_SheetActivate(ByVal Sh As Object)FlashInitializeFlashStartEnd SubPrivate Sub Workbook_SheetDeactivate(ByVal Sh As Object)FlashStopEnd SubPrivate Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)FlashStopFlashInitializeFlashStartEnd SubIf the ActiveCell has a Conditional Format rule that applies a fill color, it might not flash. The ActiveCell will stop flashing whenever you save the workbook. If you want to temporarily stop the ActiveCell's flashing, manually run the FlashStop macro. Flashing will restart when the ActiveCell is changed.


2019-10-19 06:55:31

SteveJez

JD,
If you can use macros in your sheet, place the following code module in the sheet module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Range("F1") = ActiveCell.Row
Range("F2") = ActiveCell.Column
Range("G1") = ActiveCell.Value

End Sub

And create a conditional formatting rule using the following formula

=AND($G$1="",ROW()=$F$1,COLUMN()=$F$2)

Every time you change the worksheet - enter a value, move the cursor etc. - the values in F1, F2 & G1 get updated. The conditional formatting then highlights the current cell providing it's blank. As soon as you enter a value in the active cell the AND formula of the conditional formatting returns a false (G1 is no longer blank) & and the highlight is turned off.

Obviously you can use which ever cells you like, they just need to be changed in both the Worksheet change event & the Conditional Formatting formula.

HTH


2019-10-18 10:05:37

JD

Hi,

Thank you for this.

I need to take this to another level of sophistication.

I use an excel for mac 2016 spreadsheet to do manual inventory.

I take my macbook around with me, manually count and enter them into the cell.

However, taking my eyes off the screen causes frustration when I get back to it and
have to re-find my place.

I need to have the cell that I'm currently doing the count for to flash, while awaiting
my entry of a count, and after I've entered the count and moved to the next cell,
for that next cell to flash and the previous cell to stop flashing.

So I only want the current cell to flash and then stop flashing after its value has
been entered/updated.

The cell would need to flash regardless of a previous value being in it or not.

Can you develop this?

Thanks!


2019-10-17 06:55:49

John Smith

Your instructions are not very clear for beginners. How do you run flash start? No instructions of how to do that!


2019-09-23 20:30:34

JD

Re: Excel for Mac 2016: make active cell blink

Hi,

In "Excel for Mac 2016" how to make the current cell (the one the cursor is in) blink?

I use an "Excel for Mac 2016" spreadsheet for taking inventory. I walk around with
my little laptop, count, enter, continue.

The problem is that with all the various cells in my complex-looking, multi-colored
spreadsheet, I lose my place easily and have to hunt around the page looking for
where to enter the next count.

If the current cell where the cursor is would blink, then I would have an easy way
to locate myself again.

I'd enter the count into the blinking field, cursor-down, and the next empty field
would blink, waiting for me to do my count and continue.

Do you have a way to do this?

Thanks!


2019-06-01 17:34:32

lester

What if I just want to make every cells highlighted in red for example, flash as a whole instead of picking each cells one by one? Can I do that and how?


2019-05-09 03:22:59

Oscar Bravo

Well done for going to all the trouble of programming a work-around for a feature that MIcrosoft have evidently decided they can't be bothered including as standard. It's like if I asked, "How do I turn on the heating in my car?" and the answer was "First, take out the back seats. Then install an fireplace. Finally, gather some wood and simply ignite a handy campfire. P.s. don't forget to install a chimney, or you might have to wind down the windows."


2018-01-14 10:47:59

Barry

@Phillip

Nice idea in principle but Excel doesn't update the time value in real time only, when the worksheet recalculates. That's why a macro is necessary.


2018-01-14 03:52:31

Philip

Barry's suggest to have a macro toggle a cell value between true and false made me think that maybe this whole objective CAN be achieved entirely WITHOUT Macro's ...

What if I have a cell where a formula calculates if the "second" of the time is even or odd ... that would have a toggle between true and false WITHOUT macro. Then that can be used for conditional formatting, again WITHOUT macro.

Would that work ?

Only question ... how to make this conditional on the value of the cell you want to start flashing ?


2018-01-12 16:27:34

Allan P{oe

Select the cell that you want to flash on and off. (Make sure the cell has something in it; it should not be blank.)
Make sure the Home tab is displayed on the ribbon.
In the Styles group, click Cell Styles. Excel displays a drop-down selections of pre-defined styles.
Choose New Cell Style. Excel displays the Style dialog box. (See Figure 1.)
Figure 1. The Style dialog box.
In the Style Name box enter the name "Flashing" (without the quote marks).
Using the controls in the dialog box, modify any attributes for the style, as you desire.
Click on OK.

Did exactly as outlined above at least six times. None of the six filled cells flashed.
What did I do wrong.
I am using 2007.


2017-08-17 17:25:17

Daniel W. Dykes

Allen; I used your "Flashing Macro" for an excel cell, just as you described, but instead of just the cell I wanted to blink, the whole worksheet blinked (really just the cursor).

What did I do wrong?


2017-05-20 12:14:11

Craig

I did as you instructed above for Excel 2007. The StartFlash macro did not work. When activated, the whole sheet would blink every second, but not the subject cell. Obviously I'm doing something wrong. I have no idea if you will respond or even get this since I am not a subscriber. Just thought I'd give it a shot. Sorry for the bother.

Appreciatively,

Craig


2017-04-28 02:32:01

Barry

@Seb

You don't specify which of the solutions in this tip you're trying to use, nor any symptoms. So it's not going to be easy to help you.

Do check that macros are enabled, and that the macros are located on the correct codepages.


2017-04-27 05:16:56

Seb

it does not work.... ;-(


2017-02-14 01:07:21

Sam

I need help with making it so that the empty cells on my sheet in a list flash if the cell is empty. If anyone is able to help me, please email me at sammartin2u@hotmail.co.uk I will pay £5-£10 dependant on how quickly the task can be resolved. I would do it personally but I have an important day to reach by and am already caught up with work so I need the help.

Many thanks.


2017-01-19 05:21:49

Barry

@huaimin,

There is a link to spreadsheet that you can down in my post on 5 May 2016 below.

Please make sure that macros are enabled otherwise this will not work.


2017-01-19 00:49:28

huaimin

I tried in my 2013 Excel ,but doesn't work.perhaps you could email me the spreadsheet with Flashing Cells Macros,thanks.


2016-09-30 05:22:45

Barry

@Rahul

Your comment is not very helpful. The solutions DO work if implemented correctly, and I suspect it is something in your system that is stopping things from working. You can even download the solution I proposed and try it (YOU will have to make sure macros are enabled though). There are several solutions proposed in this article which one are you trying to get working?

As all the solutions are macro based you will need to ensure that macros are enabled, otherwise none of these solutions will work. Of course, it is always possible that a User opens a workbook and disables macros, so it might be useful to organise the workbook to cope with this situation.


2016-09-30 01:51:01

RAHUL

NOT WORKING


2016-05-05 05:32:54

Steve

Paul,
I've sent you an email directly with sample file to make the cells flash when the file is opened.


2016-05-05 05:28:11

Barry

@Paul,

I have a spreadsheet that does this albeit in Excel 2010, but I've no reason to believe this won't work in Excel 2013 or 2016.

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 formula: "=Flash" and whatever formatting you desire. If you want other conditions as well, say, to only flash above a certain value then use the AND function e.g. "=AND(A1>10, Flash)" to flash for cell values over 10.

You can download a spreadsheet with these macros in them from: http://bit.ly/24thJnc Note: you will have to make sure macros are enabled for this to work as Excel by default is suspicious of any macro enabled workbook sourced from the Internet.

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


2016-05-04 10:30:24

Paul

I have Excel 2016 so I'm guessing this won't work for me. Would it be possible for you to make up a spreadsheet for me to download as I get totally baffled by your macro instructions? Or perhaps you could email me the spreadsheet.
I have a spreadsheet that has a macro built in to it that when it is open and another spreadsheet is also open it enables uppercase text to be changed to lower case for the contents of the cell and back again if required. This can be achieved in MS Word by pressing shift and F3 each time changing uppercase to lowercase and even starting with a capital letter for the highlighted word.


2016-03-08 14:43:42

mmcneal@pgwglass.om

You might try a 'Sub Auto_Open()' macro attached to that workbook.
Sub Auto_Open()
insert the flash macro
End Sub.
I use this with a message box on my default spreadsheet. It may need tweaking for the added flash macro. ?


2016-02-18 07:08:11

ANTHONY

Thanks for all the help I have been receiving from your Site. Appreciate it!


2015-06-25 08:29:24

Sat

Excellent! Worked like a charm. Thank you!
I too have the same question like Dave. I wanted to enable the flash everytime I open the excel book, not manually running the macro, any idea if this is possible please


2015-05-28 15:36:32

STEVE

INSTRUCTION ARE NOT CLEAR, STEPS ARE NOT 100% COMPLETE


2015-04-15 15:22:34

NItesh

Its Working Good.
Thanks :)

One more question how to flash the cell background(fill color)???


2015-02-18 01:56:12

Abdul

Hi,

It's not clear please explain with Screenshoot.


2014-12-21 13:01:09

MNDooley

If you are getting a:

Runtime error 9

Hit debug and if this line:

With ActiveWorkbook.Styles("Flashing").Font

is highlighted you need to change Flashing to what ever you named your style.


2014-12-18 05:22:51

Barry

It is sometimes 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 will eliminate most of the "script out of range" errors people are experiencing.


2014-12-12 17:19:57

Aldo

KC

"Subscript out of Range"

http://msdn.microsoft.com/en-us/library/aa264519%28v=vs.60%29.aspx


2014-12-09 01:51:45

Ezi Suhaini Idrus

Hi,

This works wonderfully for me to do my presentation.

However, am having one tiny problem that is to start flashing once the file is saved and emailed.

hope i can get some tips on this one.


2014-12-03 09:17:51

KC

Myself and others are getting the Run-Time Error "9" saying "Subscript out of Range" on the .ColorIndex = 5 - .ColorIndex statement in the macro. Can anyone tell me (us) how to resolve this issue?


2014-10-05 17:11:50

anand

Hi I ran this macro but I cannot stop it..message pops up -Runtime error 1004, Method 'ontime' of the object'_application'failed.

Can anyone please help


2014-09-17 12:00:59

Greg

The very first part has me confused.
How do I "dim next time as date"


2014-08-09 04:37:15

John Chapman

I cannot make this work. I get the following error - Cannot run the Macro... The macro may not be available in this workbook, or all macro's may be disabled.
I have enabled Macros and the Macro is in the workbook!!


2014-07-16 04:50:31

michael

where are the responses to questions?


2014-07-03 14:40:29

Girish Y Sutar

Please teach me...once again cause unable to run macro


2014-06-17 06:23:19

shamsher

runtime error 9

script out of range



yellowcolor on this line

.ColorIndex = 5 - .ColorIndex


2014-06-13 07:59:47

Sk Reyazuddin

Hi Allen!
Its a great knowledge for me to use "Flashing" function in my workbook.
Its really great.
But it has not actual I needed. When I apply "Flashing" the whole workbook is set to flash. I only want to flash some specified cells.
Will you please help me out from their?

Thanks & looking forward.

Sk Reyazuddin


2014-03-06 06:21:09

Shubhabrata Dey

HI,

What if I have a conditional formatting on a cell and its value is variable i.e. it will keep on changing based on certain calculations and I want the entire cell/text in the cell to blink even though the background color of the cell changes based on its value? Your above code works only when the Cell Style has been defined at the very beginning. I do not wish to define the Cell Style as I want my cell to be dynamic.

Please help.

Many thanks.


2014-02-21 16:31:55

Mark

The Styles group from my Home ribbon does not display New Cells Style, only Conditional Formatting and Format A Table. Is there another way to edit cell styles?


2014-02-05 05:00:33

Nitin

Can the flash format be activated conditionally ?
Say if the value in each of a particular range of cells goes below a specified limit, the value flashes - drawing attention......


2014-02-03 12:10:05

Dave

This worked great, but is there a way to make the macro automatically start when the workbook is open?


2014-02-03 09:41:00

jaub

Works great! Thanks - this is fun.


2014-02-03 04:36:09

Andrew

It work fine for me as well as long as I put into a module rather than try to make a sheet specific macro.


2014-02-01 17:28:00

Jack

Dave:
It worked just fine for me.
At first, it did not.
I needed to apply the "Flashing" style to the cell, then run the macro.


2014-02-01 16:03:25

Dave Hansen

I copied these macros exactly as shown and pasted them in the individual macro

I made sure the cell involved was formatted as shown in the instructions.

Upon running the "Start Flash" macro nothing happens.
I placed a stop in the sub just before the "End With" statement.

If .ColorIndex = xlAutomatic Then .ColorIndex = 3
.ColorIndex = 5 - .ColorIndex

When the macro stopped I then looked at the beginning variable ".colorindex ="
That variable had a value of "1" not "Automatic" so the If statement fails and the macro stops without exicution of the actual flashing operation.

This causes a HMMMM in my mind. Something is not right somewhere. If the Format is correct then something in the code does not create an error but is not finding the "xlAutomatic" correctly.

????????
Dave Hansen


2014-02-01 08:56:56

Barry Fitzpatrick

Note: Also because macros are running every second the "Undo" stack will be cleared every second, rendering it unusable.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu 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.