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

by Allen Wyatt
(last updated January 11, 2020)

13

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

Automatically Adding Captions

Word can automatically add captions to various elements of your document, such as tables or figures. Here's how to ...

Discover More

Non-Printing Hyperlinks

Karen is having problems getting hyperlinks to print in a document on her home computer. There are only a limited number ...

Discover More

Selecting a Field

Do you need to select a field? It is as simple as selecting a single character, as this tip explains.

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Number Formatting Shortcuts

Keyboard shortcuts can save time and make developing a workbook much easier. Here's how to apply the most common of ...

Discover More

Using Fractional Number Formats

If you want information to display on the screen using fractions instead of decimals, you're in luck. Excel provides ...

Discover More

Understanding Cell Indenting

Formatting a cell could, if you desire, also include the indentation of information within the cell. This tip examines ...

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 2 + 9?

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.