Loading
ExcelRibbon.Tips.Net ExcelTips (Ribbon Interface)

Flashing Cells

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.

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.

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.

Related Tips:

Tame Your Data! ExcelTips: Filters and Filtering provides all the details necessary to let you manage large sets of data with confidence and ease. Its information-packed pages demonstrate how to use the two types of filters provided by Excel: AutoFilters and advanced filters. Check out ExcelTips: Filters and Filtering today!

 

Comments for this tip:

Shubhabrata Dey    06 Mar 2014, 06:21
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.
Mark    21 Feb 2014, 16:31
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?
Nitin    05 Feb 2014, 05:00
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......
Dave    03 Feb 2014, 12:10
This worked great, but is there a way to make the macro automatically start when the workbook is open?
jaub    03 Feb 2014, 09:41
Works great! Thanks - this is fun.
Andrew    03 Feb 2014, 04:36
It work fine for me as well as long as I put into a module rather than try to make a sheet specific macro.
Jack    01 Feb 2014, 17:28
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.
Dave Hansen    01 Feb 2014, 16:03
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
Barry Fitzpatrick    01 Feb 2014, 08:56
Note: Also because macros are running every second the "Undo" stack will be cleared every second, rendering it unusable.

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 3+4? (To prevent automated submissions and spam.)
 
          Commenting Terms
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2013)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2013)

Our Products

Premium Newsletters

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2014 Sharon Parq Associates, Inc.