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)

14

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

Controlling Scroll Bars

Scroll bars are helpful if you have a document that won't fit entirely within the program window. Here's how to turn off ...

Discover More

Displaying ScreenTips

ScreenTips are those small, yellow boxes that appear when you hover over different objects in Word. You have complete ...

Discover More

Saving Search and Replace Information in a Macro

You may want to save a user's existing Find and Replace settings before changing them in your macro. This tip examines ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Automatically Formatting for Decimal Places

Cell contents and cell formatting are, in Excel, largely independent of each other. You can enter something in a cell and ...

Discover More

Partially Blocking Social Security Numbers

Need to protect a series of Social Security Numbers in a worksheet? The techniques provided in this tip might be a good ...

Discover More

Identifying Merged Cells

Merging cells is a common task when creating worksheets. Merged cells can play havoc with the normal functioning of some ...

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 nine more than 2?

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.