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.
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:
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.
Leave your own comment:
Comments for this tip:
Steve 05 May 2016, 05:32
I've sent you an email directly with sample file to make the cells flash when the file is opened.
Barry 05 May 2016, 05:28
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
If ThisWorkbook.Names("Flash") = "=1" Then
ThisWorkbook.Names("Flash").Value = "=0"
ThisWorkbook.Names("Flash").Value = "=1"
HighlightTime = Now + TimeValue("00:00:01") 'SetTime for +1 secs
Application.OnTime HighlightTime, "RefreshHighLight"
and on the "ThisWorkbook" codepage:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime HighlightTime, "RefreshHighLight", , False
Private Sub Workbook_Open()
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.
Paul 04 May 2016, 10:30
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.
email@example.com 08 Mar 2016, 14:43
You might try a 'Sub Auto_Open()' macro attached to that workbook.
insert the flash macro
I use this with a message box on my default spreadsheet. It may need tweaking for the added flash macro. ?
ANTHONY 18 Feb 2016, 07:08
Thanks for all the help I have been receiving from your Site. Appreciate it!
Sat 25 Jun 2015, 08:29
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
STEVE 28 May 2015, 15:36
INSTRUCTION ARE NOT CLEAR, STEPS ARE NOT 100% COMPLETE
NItesh 15 Apr 2015, 15:22
Its Working Good.
One more question how to flash the cell background(fill color)???
Abdul 18 Feb 2015, 01:56
It's not clear please explain with Screenshoot.
MNDooley 21 Dec 2014, 13:01
If you are getting a:
Runtime error 9
Hit debug and if this line:
is highlighted you need to change Flashing to what ever you named your style.
Barry 18 Dec 2014, 05:22
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.
Aldo 12 Dec 2014, 17:19
"Subscript out of Range"
Ezi Suhaini Idrus 09 Dec 2014, 01:51
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.
KC 03 Dec 2014, 09:17
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?
anand 05 Oct 2014, 17:11
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
Greg 17 Sep 2014, 12:00
The very first part has me confused.
How do I "dim next time as date"
John Chapman 09 Aug 2014, 04:37
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!!
michael 16 Jul 2014, 04:50
where are the responses to questions?
Girish Y Sutar 03 Jul 2014, 14:40
Please teach me...once again cause unable to run macro
shamsher 17 Jun 2014, 06:23
runtime error 9
script out of range
yellowcolor on this line
.ColorIndex = 5 - .ColorIndex
Sk Reyazuddin 13 Jun 2014, 07:59
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.
Shubhabrata Dey 06 Mar 2014, 06:21
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.
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
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.
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.