locked
Creating a macro with a flashing cell color option? RRS feed

  • Question

  • Hi!

    I'm busy making a macro in Excel 2010 and I'm wondering if there is any possible way to make a flashing cell that changes from color in a few seconds?

    If possible, please give me the Visual Basic code for this.

    Thank you!

    Saturday, May 17, 2014 9:42 PM

Answers

  • Yes, and it's quite easy. However, I would strongly recommend against using such a macro. For one thing, it will keep clearing the clipboard, making it difficult to cut/copy and paste consistently. For another, flashing cells soon become more annoying than useful; better to use conditional formatting to change the cell background to a single colour (eg red or yellow) if a warning is needed.

    Here is a macro that makes the borders of cell C3 change every 5 seconds, for 5 times. It can be adapted to make the text flash, or the fill colour. You can also change the frequency and duration.

    Dim i as Long
    Sub FlashCell()
    Dim aCell As Range
    Set aCell = Cells(3, 3)
    With aCell.Borders
      If .LineStyle = xlContinuous Then
        .LineStyle = xlNone
      Else
        .Weight = xlMedium
        .LineStyle = xlContinuous
      End If
    End With
    If i Mod 10 = 0 Then Exit Sub
    i = i +1
    Application.OnTime Now + TimeValue("0:00:01"), "FlashCell"
    End Sub

    Alternatively, to make a cell flash every second if it's value is 1, use conditional formatting in the target cell, with the formula:

    =AND(MOD(VALUE(TEXT(NOW(),"ss")),2)=0,A1=1)

    and set the conditional format to whatever font/background/border arrangement you want. Then add the following macro to the workbook:

    Sub FlashCell()
      Range("A1").Calculate
      Application.OnTime Now + TimeValue("0:00:01"), "FlashCell"
    End sub

    Run the macro and the target cell will flash if your criterion is met.

    Note: change "A1" in the above references to any cell that does not contain a formula - unless you actually want the nominated cell to recalculate every second.

    Once the above has been done, all you need to do to make other cells flash is to copy the conditional format to those cells, changing nothing more than the "A1=1" argument to whatever 'flash' criterion you want. For example, if you want more than one cell to flash if "A1=1" , change "A1=1" to "$A$1=1" and apply/copy the format to those cells also.

    You could even get fancy, having multiple sequential flashes of the cell, by adding conditional format arguments and making the conditional format formulae, in turn. For example:

    =AND(MOD(VALUE(TEXT(NOW(),"ss")),4)=1,A1=1)
    =AND(MOD(VALUE(TEXT(NOW(),"ss")),4)=2,A1=1)
    =AND(MOD(VALUE(TEXT(NOW(),"ss")),4)=3,A1=1)


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Marked as answer by Ynothna Sunday, May 18, 2014 2:48 PM
    Sunday, May 18, 2014 6:35 AM