Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. 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

Written by Allen Wyatt (last updated January 11, 2020)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


22

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 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.

With the style now defined, you need to apply that style to any cells you want to "flash." Make sure you don't apply the style to any other cells.

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 <> 2 And .ColorIndex <> 3 Then
            .ColorIndex = 3
        End If
        .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.

The macro works by modifying the style you previously defined, earlier in this tip. (Make absolutely sure that you name the style "Flashing," as indicated in step 5.) All it does is to change the ColorIndex property so it switches between two values—3 and 2. That change in the style results in the flashing effect as the text color changes from red (3) to white (2) and back again.

There is one other 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. It is this value that is used to specify the next time (one second in the future) that the StartFlash should be re-run.

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, 2013, 2016, 2019, and Excel in Microsoft 365. 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

Changing the Perspective of Your Chart

Microsoft Graph can be a handy way to add quick and dirty charts to your document. When working with 3-D charts, you can ...

Discover More

Setting Decimal Tabs in a Table Using the Keyboard

Most people use the mouse to set tab stops in the paragraphs in a table. If you prefer to not use the mouse, then you'll ...

Discover More

Setting Data Validation Input Messages

When using data validation, you might want to have Excel display a message when someone starts to enter information into ...

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 (ribbon)

Rounded Corners on Cells

As you are formatting a worksheet, Excel allows you to easily add borders to cells. Adding rounded corners to cells is a ...

Discover More

Checking All Cell Formatting in VBA

When your macro checks the formatting used for a cell, it needs to be careful that the type of formatting being checked ...

Discover More

Creating 3-D Formatting for a Cell

The formatting capabilities provided by Excel are quite diverse. This tip examines how you can use those capabilities 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}] (all 7 characters, in the sequence shown) 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 8 - 3?

2022-02-01 14:41:53

J. Woolley

@JD
Since you have patiently waited 14 months for an answer, perhaps this will help. Download the following PDF:
https://drive.google.com/file/d/1qDMNcybXYJ_Qr3VvctsGKYxqTamDKrDj/view
It describes a method that is different from the one provided in the Tip. Therefore, remove any VBA copied from the Tip and start over; do not use the "Flashing" style. This method has not been tested on a Mac, but it should work OK in that environment.
Also, see https://sites.google.com/view/MyExcelToolbox/


2022-01-31 12:17:57

JD

@ J. Woolley
"Did you review the previous comments before posting your latest?"

I did, though, not being too tech-savvy, I could not follow them much.
I don't understand how to program, but, I am good at following directions :-)))

If you can hold my hand and tell me what to do, step by step, I can do that.

I apologize for my ignorance and for causing you to roll your eyes up in your head!

But getting this to work, in my MacBook using Office for Mac 2016, would really be a plus !

Thanks!


2022-01-31 09:59:50

J. Woolley

@JD
Did you review the previous comments before posting your latest?


2022-01-30 18:17:34

JD

I created an "Excel for Mac 2016" spreadsheet for supply ordering.

The supply order depends on the current stock on hand, which has to be
manually counted.

The spread sheet is rather extensive. Its easy to lose one's place when
taking your eye's off the screen, going to get a count and returning to the
screen. I'm always playing the, "Now where was I?" game - no fun anymore!

It would be great to flash the empty cell (and only that cell) that is awaiting
data entry and after entering the inventory count to then just cursor-down
to the next cell below, have this next cell flash, and the cell (and all cells)
above stop flashing. Only the current "active" cell awaiting data-entry
should flash so when I come back I know just where I left off.

Although an "Excel for Mac 2016" newbie I was able to watch a few short
YouTubes and apply your fix into VBA and get the cell to flash.

It flashed the cell about once per second.

But, when I cursor'd down to the next cell below, in the same column,
not only did this cell not flash, but the cell above continued to flash.
So this is a start, but not over the finish line yet.

How to have the current cell automatically stop flashing after pressing
the cursor down key, or tab key, or enter key after a value is entered,
and have the next cell below start flashing to provide the visual clue
I need to stop playing "Now where was I?". LOL!

I hope I've given you a clearer idea of what I'm looking for.

Also, I tried following your fixes above.

I got through creating and applying the Style to the cells I need to flash.

But I'm stuck on how to create a macro in Excel for Mac 2016.

I have the Developer tab open. I clicked Macros, named it Flash1 but
when I tried to add your macro from above, it won't paste. But MS
Visual Basic opened. I do not know how to work in Visual Basic.

Much appreciated if you can post a detailed walk-through to make
this work!

Thanks!


2021-08-07 11:56:03

J. Woolley

@Sharif
Open your workbook in Excel, then press Alt+F11 to open the VBE (Visual Basic Editor). You should see the VBAProject frame on the left. Right-click the VBAProject for your workbook and pick Insert > Module. This should open a new Code frame on the right. Copy the macro VBA (Visual Basic for Applications) code into the Code frame. Save the workbook as Macro-Enabled (*.xlsm). Press Alt+F8 to run a macro.
See https://www.ablebits.com/office-addins-blog/2013/12/06/add-run-vba-macro-excel/


2021-08-06 09:50:09

Sharif

Followed everything to

"Now create the macros (there are two of them), as follows: …." where I got lost. What is a macro? Where do I add all that formula.

I can't find a single web site that shows every step.


2021-07-10 13:23:41

J. Woolley

@Shane
To set the flashing frequency in milliseconds, replace the Tip's VBA code with the following. This assumes you are using Excel 2010 or later (VBA7) and Windows.

Private TimerID As LongPtr
Declare PtrSafe Function SetTimer Lib "user32" _
    (ByVal Hwnd As LongPtr, ByVal nIDEvent As LongPtr, _
    ByVal uElapse As Long, ByVal lpTimerFunc As LongPtr) As LongPtr
Declare PtrSafe Function KillTimer Lib "user32" _
    (ByVal Hwnd As LongPtr, ByVal nIDEvent As LongPtr) As Long

Sub Flash_TimerProc _
    (ByVal Hwnd As LongPtr, ByVal wMsg As Long, _
    ByVal idEvent As LongPtr, ByVal dwTime As Long)
    On Error Resume Next
    KillTimer 0, TimerID
    TimerID = 0
    Application.Run "StartFlash"
End Sub

Sub StartFlash()
    With ActiveWorkbook.Styles("Flashing").Font
        If .ColorIndex <> 2 And .ColorIndex <> 3 Then
            .ColorIndex = 3
        End If
        .ColorIndex = 5 - .ColorIndex
    End With
' kill pending Timer, then start a new Timer
    If TimerID <> 0 Then KillTimer 0, TimerID
    Const msec As Long = 500 ' set Timer milliseconds
    TimerID = SetTimer(0, 0, msec, AddressOf Flash_TimerProc)
End Sub

Sub StopFlash()
    If TimerID <> 0 Then KillTimer 0, TimerID
    ActiveWorkbook.Styles("Flashing").Font.ColorIndex = xlAutomatic
End Sub

Adjust the line that begins with Const msec....
Also, see https://sites.google.com/view/MyExcelToolbox/


2021-07-09 11:15:04

Shane

How can I make this flash faster than a second?


2020-11-23 15:12:45

JD

Dear Sir,

I created an "Excel for Mac 2016" spreadsheet for supply ordering.

The supply order depends on the current stock on hand, which has to be
manually counted.

The spread sheet is rather extensive. Its easy to lose one's place when
taking your eye's off the screen, going to get a count and returning to the
screen. I'm always playing the, "Now where was I?" game - no fun anymore!

It would be great to flash the empty cell (and only that cell) that is awaiting
data entry and after entering the inventory count to then just cursor-down
to the next cell below, have this next cell flash, and the cell (and all cells)
above stop flashing. Only the current "active" cell awaiting data-entry
should flash so when I come back I know just where I left off.

Although an "Excel for Mac 2016" newbie I was able to watch a few short
YouTubes and apply your fix into VBA and get the cell to flash.

It flashed the cell about once per second.

But, when I cursor'd down to the next cell below, in the same column,
not only did this cell not flash, but the cell above continued to flash.
So this is a start, but not over the finish line yet.

How to have the current cell automatically stop flashing after pressing
the cursor down key, or tab key, or enter key after a value is entered,
and have the next cell below start flashing to provide the visual clue
I need to stop playing "Now where was I?". LOL!

I hope I've given you a clearer idea of what I'm looking for.

Thanks!


2020-06-08 02:44:05

Barry

@Keith

I'm not sure if changing styles can be made conditional. It might be available in newer versions of Excel but isn't available in Excel 2010 that I use.

However, if you use the solution that I've put together it absolutely can be conditional and in all versions of Excell (except possibly the Mac versions). In fact if you download the sample workbook (see below) the flashing is conditional on the cell value, trying changing it to 1.


2020-06-07 05:33:41

Keith

Hi

Could this be expanded to be conditional?

Either via Conditional Formating or through "IF" statements?

If so, now?

Thanks

Keith


2020-06-07 02:55:08

Barry

@raghavan

If you're using the code in the tip then all you have to do is call the "StartFlash" macro in a routine called Workbook_Open an save it in the ThisWorkbook codepage.

If you use my macros this is already closed.

You do need to check that macro security is set to enable macros in the security settings, but it better to designate the folder where you store the workbook is "trusted".


2020-06-07 01:46:04

raghavan

Hi,

I tried the flashing it worked well. But I need to run the macros each time to start flashing, what i need it as soon as i open my excel sheet one designated cell of my has some information that alone need to flash automatically each time i open without running the macros, is this possible.


2020-02-13 02:38:00

Barry

@Brian

I've run this using Excel2010 on Win10 with no problem.

So I'm not sure why you're getting the error. I don't think there are any special libraries to be referenced, the OnTime method is standard as I recall.


2020-02-11 14:40:39

Brian Hershman

Barry, I have downloaded the workbook you mentioned and started to run it. It flags the error "Application.ontime is invalid"! I am using Excel 2010 under Windows 7 which is , I thought, one of the valid versions; but the Object Browser confirmed that there is no such property!


2020-02-10 02:38:27

Barry

@David Randall

Try my version (see the post below dated 20-Jan-20) it doesn't use styles. I haven't tried on a Mac so I'd be interested to know if it works. You download the test file to save you copying the code.


2020-02-09 02:36:36

Barry

@Brian Hershman

See my alternative solution below, download and try the example spreadsheet. The solution as a matter of course uses conditional formatting so it is only a matter of adding an additional condition to the formula using the AND operator.


2020-02-08 20:26:09

Brian Hershman

Hi Allen, I want to use tip 7223 to CONDITIONALLY format any rows that contains a given value in a given column. I have defined the flashing style and tried it out:
1. as an absolute format it does seem to work, although it needs brighter colours to make it noticeable.
2. But I cannot see how to use it in/as a conditional format.

If you have already discussed this problem elsewhere, please just refer me to the relevant link.

Many thanks,
bsh53g@gmail.com
Brian H.


2020-01-14 05:19:23

Rod Grealish

You may need to ensure that spreadsheets with flashing cells or text (or anything else for that matter) are not going to cause seizures in those with epilepsy. There may be regulations or laws in your jurisdiction which control flashing features especially where spreadsheet may be publicly viewed.


2020-01-13 13:43:59

David Randall

Hello,

I tried following your guidance on how to create flashing cells but it was not possible to do it. I have the Mac version of MS Office and the latest version of Excel I have is 16.32 which does allow permit creation of new styles.

Can you advise how to can create Flashing Cell with current software I have?

Best wishes

David


2020-01-11 15:18:50

Barry

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.


2020-01-11 13:28:55

Ronmio

Would it make sense to just place the StartFlash and StopFlash macros in my Personal.XLSB so that I can get flashing anywhere I use the Flashing style?


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.