How to make a cell flash in Excel and why I wouldn’t do it 19


image-Flashing Cells

 

I was asked recently by a colleague of mine “is there a way in which we can make a cell flash in Excel to highlight some pertinent information?” The idea behind the question was that the data in the spreadsheet is entered and maintained by an employee and then provided to their boss to actually work with the information.

So… was there a way in which to highlight areas in the spreadsheet for the boss so that they did not have to scour the entire document to find the information they needed to look at?

 

To answer the question – yes, there is a way to make a cell background flash in Excel, however, as I’ll discuss later, I would not apply this as a solution for them in this particular case.

 

It comes down to... Just because you can, doesn't mean you should! Click To Tweet

 

How to make a cell flash in Excel

If we are trying to achieve this effect in Microsoft Word, it is actually very straight forward. Simply, select Format | Font from the menu, click the Text Effects tab, and choose Blinking Background – Viola done!! Nice and simple and very easy to achieve.

Now, you might expect that it would be just as simple to achieve the same in Microsoft Excel, but unfortunately (or fortunately as you’ll see) that is not the case. It is, in fact, a LOT more difficult and requires us to jump into the world of VBA (Visual Basic for Applications) and do a little macro programming.

So how do we actually do it…

For our example, we are going to make the cell A3 have a flashing background.

Step 1 On the Developer Tab, select the Visual Basic Editor from the menu.
image-Flashing Cells - Visual Basic Editor
Note: The Developer Tab is not shown by default in Excel, you need to add it in. If you do not have the Developer Tab showing, follow this short tutorial.

 

Step 2 Right-click the VBA Project item in the tree at left and choose Insert | Module from the pop-up menu.
image-Flashing Cells Visual Basic Editor

 

Step 3 Type or copy and paste the following code into the module
Public NextFlash As Double
Public Const FlashRng As String = "Sheet1!A3"

Sub StartFlashing()
   If Range(FlashRng).Interior.ColorIndex = 3 Then
      Range(FlashRng).Interior.ColorIndex = xlColorIndexNone
   Else
      Range(FlashRng).Interior.ColorIndex = 3
   End If
   NextFlash = Now + TimeSerial(0, 0, 1)
   Application.OnTime NextFlash, "StartFlashing", , True
End Sub

Sub StopFlashing()
   Range(FlashRng).Interior.ColorIndex = xlColorIndexNone
   Application.OnTime NextFlash, "StartFlashing", , False
End Sub
image-Flashing Cells Visual Basic Editor

 

Step 4 We now need a way to turn the flashing on and off, so we’ll add 2 buttons to our spreadsheet. In the Excel spreadsheet, click the Developer Tab and then make sure “Design Mode” is selected.
image-Flashing Cells - Design Mode

 

Step 5 Click Insert then click the Button (Form Control) option
image-Flashing Cells - Insert Button Form Control

 

Step 6 Click on your spreadsheet (around cell C2, but it doesn’t matter exactly where) to add the button. The Assign Macro dialogue box will show. Select the “StartFlashing” macro and then click OK.
image-Flashing Cells Macro Assign

 

Step 7 Right click on the button you just added and select Edit Text, from the pop-up menu, to give the button a more meaningful name. Edit the text to something like “Start Flashing”. When complete, click off the button to anywhere else on your spreadsheet to apply the changes.
Note: you can also modify the size of this button at this stage as well if you like.
image-Flashing Cells Edit Button Text

 

Step 8 Repeat steps 5 through 7 to add a second button onto your spreadsheet, but this time in step 6, assign the “StopFlashing” macro to the button and in step 7, rename your button to something like “Stop Flashing”.

 

Ok, we’re done and don’t forget to save your workbook.

 

Click the “Start Flashing” button and the background of cell A3 should now start to flash! Click the “Stop Flashing” and the flashing should stop.

 

How does it work?

So how does it work? First, the constant FlashRng defines the range of cell(s) that will flash. You can make different cells flash by changing this value. If the flash range has a red background, this macro sets it back to normal; if not, it makes the background red. Then it sets itself up to be called again in one second using the application’s OnTime method.

Next, we check the background colour of our range of cells. If the flash range has a red background (i.e. ColorIndex=3), then the macro changes it back to normal; if the background is not red, the code then makes the cell background red.

The TimeSerial  function returns a numeric time-value corresponding to the number of hours, minutes, and seconds passed to it as input. TimeSerial takes only whole numbers, so the shortest time period it can calculate is one second which we use here.

We set the NextFlash variable to the current time (NOW) plus one second (TimeSerial(0,0,1)), and we call the application object’s OnTime method to launch StartFlashing again at that time (i.e. every second in our case).

Each time the macro is called the cell background of our FlashRng is changed from red to normal or vice versa.

The StopFlashing macro simply restores the normal background and calls OnTime to cancel the pending event. To cancel an event, you have to pass the exact same time that was used to schedule it, which is why we had to store that time in the public variable available to both macros.

 

That’s a lot of work just to get flashing text, but it does the job. And of course, you can apply it to any range you like by changing the value of the FlashRng constant.

So why wouldn’t I use this as a solution??

So you are probably wondering now “Why after all that work would we not use this as a solution?”

My reasoning is actually quite straight forward.

In order for Excel to make a cell flash, the spreadsheet MUST constantly run your code (as I explained above, our macro is called every second, in order to “flash” the cell background) so it can keep checking your criteria to determine whether or not to apply any formatting. This may not seem a big deal at first, particularly if you are only dealing with small spreadsheets. However, constantly have code running in your spreadsheet will add VERY SIGNIFICANT load onto the processing of it and if you work with large and/or complex spreadsheets that undertake lots of calculations, putting unnecessary load on them is a BAD thing and something you want to avoid wherever possible.

 

So there you have it, yes we can make a cell’s background in Excel flash, but I generally wouldn’t recommend actually applying this to any of your spreadsheets.

 

It comes down to… Just because you can, doesn’t mean you should!

 

Please Share

If you liked this article or know someone who could benefit from this information, please feel free to share it with your friends and colleagues and spread the word on Facebook, Twitter or Linkedin.

 

Download

You can download a PDF transcript of this tip by clicking the following link – How to make a cell flash in Excel and why I wouldn’t do it!.

You can also download a copy of the spreadsheet I used in this article so you can explore this tip further – How to make a cell flash in Excel and why I wouldn’t do it!.

 

 

  • Goodness. This is complex. I just tend to highlight or bold to draw attention to areas within a spreadsheet.

    • Hi Ursula, yes there is nothing stopping you from manually doing exactly that, however, the issue comes to play when you are dealing with LARGE datasets (spreadsheets that may contain hundreds if not thousands of lines of data) that you want to have data/trends/etc highlighted automatically. Doing so manually is VERY time consuming and prone to human error.

      Comes back one of my favourite sayings… “Spreadsheets should WORK for you and not you for them!!”

  • This is quite the process. And I definitely agree that just because you can, doesn’t mean you should. That applies to many areas in life! Great post!

    • It certainly is Monica, considering if you want to do a similar thing in say Microsoft Word it a simple 3-4 step process.

  • Josh s

    Would this work in google sheets? If so, is there a way to have it start flashing at a certain time? I have multiple people looking at a spreadsheet and need them to fill in data at 11:30.

    • Hi Josh, Yes there is no reason why you couldn’t do a similar thing in Google Sheets. However, the above code is written in Visual Basic for Applications (VBA) and the Google Sheets uses Appscript. These two code bases are not interchangeable – in that you can’t use one in the other without a lot of modifications.

  • Tim Smith

    so far 1 issue…you did not point out that it wont work if you copy your code with public in it..i had to change that to private…otherwise seems to work fine..

    • Hi Tim,

      Not sure why the code didn’t work for you as it is as it works fine as it is published.

      The reason we declared the 2 PUBLIC variables is so that they could be used (if you want them to be used) in modules / sub routines other than the one where this code resides.

      By making the variables PRIVATE they then can’t be accessed outside of this module.

      Do you happen to have other variables with the names of NextFlash or FlashRng in any other sub or module in your spreadsheet, as this could be one reason why it didn’t work for you??

      If you download a copy of the sample worksheet (link in the above article), you’ll see a working example of the code exactly as it is shown above.

      • Tim Smith

        thanks so much everything works fiine..however, i do agree that it would slow things down, so i would agree with you on not using it…

  • Natalia Leymaster

    Is there a way to make the background look yellow or a color other than white in between flashes?

    • Hi Natalia,

      That is very straight forward to do. All you need to do is change “xlColorIndexNone” in the code to whatever colorindex value you are after.

      Range(FlashRng).Interior.ColorIndex = xlColorIndexNone (note this line appears in both the Startflashing() and StopFlashing() sub’s).

      If you check out this link, it will show what the 56 colorindex colors are that you can use in your VBA http://www.excelsupersite.com/what-are-the-56-colorindex-colors-in-excel/

      • Natalia Leymaster

        Thank you!

        • No problem. Glad we could help.

  • nice excel trick!

    • Glad you liked it

  • Kaz

    Please help me, I have been able to make different cells flash in the same sheet. But I need it to Flash in different Sheet under same Workbook. Kindly assists.

    Thank you so much for your profound effort.

    Regards,

    • Hi Kaz,

      Send me an email with what you have and I’ll take a look.

      email: brian@excelsupersite.com

      • Kaz

        Thank you Brian, I have sent you the file am working on.

        • Kaz

          Hi Brian,

          You are amazing, I received the modified and it’s really helpful.

          Thank you so much.

          Regards,