ABC ~ All 'Bout Computers
The Online Web-azine for Computer Enthusiasts
-- brought to you by

contents page for this issue

Chad's Macro Mania
~~Chad K. Welch

Blinking Conditional Formats in Excel

I hope that you all had a wonderful Holiday Season. I certainly did. It is hard getting back to the regular rhythm.

A couple of issues ago I discussed conditional formats in Excel
(see http://personal-computer-tutor.com/abc2/v18/chad18.htm  ).
I promised that I would follow that article with one on blinking formats. Sorry that I didn't have the article put together by the time the December issue hit the presses. I thank those of you who emailed me wondering about the article and apologize again.

Blinking formats have always been hot issues in Excel. Hopefully Microsoft will build it into one of their future releases. Until they do, I have a work around. Just be forewarned that blinking conditional formats may slow your workbook down. If you have a large workbook with many formulas, blinking conditional formats may not be your best answer. That said; let's take a look at how they work.

The most crucial piece of blinking formats is the OnTime method. The method is typically called using the Application object: Application.OnTime. It accepts four parameters, two of which are optional. From the help files for OnTime:

EarliestTime  Required Variant. The time when you want this procedure to be run.

Procedure      Required String. The name of the procedure to be run.

LatestTime    Optional Variant. The latest time at which the procedure can be run. For example, if LatestTime is set to EarliestTime + 30 and Microsoft Excel is not in Ready, Copy, Cut, or Find mode at EarliestTime because another procedure is running, Microsoft Excel will wait 30 seconds for the first procedure to complete. If Microsoft Excel is not in Ready mode within 30 seconds, the procedure won't be run. If this argument is omitted, Microsoft Excel will wait until the procedure can be run.

Schedule       Optional Variant. True to schedule a new OnTime procedure.

False to clear a previously set procedure. The default value is True.

Place the OnTime method at the end of your sub procedure to call the same procedure and create a repeating sequence:

Public Sub Blink()

    ‘Code here to format the cells (discussed below)

    Application.OnTime Now() + TimeValue(“00:00:01”), “Blink”
End Sub

Whenever 'Blink' is run it will tell Excel to run the same procedure again roughly 1 second after it finishes the procedure. While this code works, it is typically a better idea to use a variable for the time rather than Now() + TimeValue incase you want to stop the procedure from running (like when you close the workbook). It is also a good idea to make the variable global so that it can be used by other procedures. Add the global variable before any lines of code in your module: Dim BlinkTime As Date.

Now we are ready to add the blinking. This concept is pretty straight forward. We will have two formats (format A and format B) that we will 'blink' through. The first check that we will perform is to check if the cell has format A. If it does then we will apply format B. If it doesn't then we will apply format A. In pseudo-code that is:

If Cell.Format = A Then

    Cell.Format = B

Else

    Cell.Format = A

End If

Replacing the pseudo-code with real cells and real formats is pretty straightforward. Now we can move on to conditional blinking. Let's assume that format B is the 'plain-Jane' format - the format that will be displayed when the cell doesn't meet any of the conditions to blink. Format A, then will be the 'changing' format. In addition, format A will have a few conditions, so we can say a cell satisfies Format A1, A2 or A3, ….

Now we will run the same test on the cell: does it have format B? If it doesn't then we will apply format B. If it does then we may need to apply a conditional format, so run another test. Does it meet condition 1? If so, apply format A1. If not, does it meet condition 2? If so, apply format A2, and so on. If the cell does not meet any of the conditions then we will apply format B to it so that it doesn't blink. In pseudo-code:

If Cell.Format = B Then

    If Cell = Condition 1 Then

        Cell.Format = A1

    ElseIf Cell = Condition 2 Then

        Cell.Format = A2

    ElseIf Cell = Condition 3 Then

        Cell.Format = A3

    Else

        Cell.Format = B

    End If

Else

    Cell.Format = B

End If

At the bottom of the article I've attached a sample code that replaces the pseudo-code with actual cells and formats.

I mentioned earlier that we should use a variable for the time so that we could stop it from running when we closed the workbook. To do that we will add a Workbook_BeforeClose event procedure that stops the sub routine. Also, we've seen how the OnTime method can be used to create a repeating sequence, but the procedure has to be called before the OnTime method can schedule a time to repeat the procedure. To do that, we will call the routine from the Workbook_Open event. Double click the 'ThisWorkbook' module in the Project Explorer of the VBE and paste the following lines of code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Application.OnTime BlinkTime, "Blink", , False

End Sub

Private Sub Workbook_Open()

    Call Blink

End Sub

There are a couple of things that you should keep in mind that may affect how and when you use blinking formats:

1. The formats may slow down large or complex workbooks.
2. Changing the formatting in this manner changes the workbook, so when you close it, you will be asked if you want to save your changes, even if you haven't made any changes to the workbook.
3. Because it uses macros, users will be asked if they want to enable macros when they open the workbook. If they choose no, the blinking will not work.

Happy coding! Until next month.

Sample Code for Blinking Conditional Formats:

• Paste the following lines in the 'ThisWorkbook' module:

Send me an idea I can use for this column and if you are the first to submit the idea I'll send you $5.00. Just make sure to put "Idea for ABC" in the subject line, so that my email filter will catch it.

Chad K. Welch works as a technician/enabler in Utah.  He is available for consulting or application programming with Microsoft Office and VBA.  Do you have a question or tip you’d like to have Chad address in this column?  Send an email to linda@personal-computer-tutor.com or contact him directly at chad@welchkins.com for more information.