AboutBob Umlas Expertise I`m a Microsoft Excel MVP (Most Valuable Professional) and have been since the inception of the program in 1995. I can answer every kind of Excel question except: API, Importing/exporting to other programs (powerpoint, word,...)
Experience Worked with MS Excel since version 0.99 (on the Mac!). Was contributing editor to Excellence Magazine, having written >300 articles. John Walkenbach said of me "I finally met someone who knows as much about Excel as I do."
Publications Excellence, The Expert, Microsoft
Awards and Honors MVP
Led sessions for the Convergence 2004-2006 seminar on Excel tips & tricks
Expert: Bob Umlas Date: 10/10/2007 Subject: Excel blinking cells
Question Bob,
I've succesfully used the following to have Excel blink a cell:
Right-click the sheet tab, select View Code, paste this in:
Private Sub Worksheet_Calculate()
If Range("B10").Value > 0 Then
Blink "B8"
Else
Range("B8").Interior.ColorIndex = 0
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Application.Run Me.CodeName & ".Worksheet_Calculate"
End Sub
Insert a regular module, and paste this in:
Sub Blink(cell As String)
If Range(cell).Interior.ColorIndex = 6 Then
Range(cell).Interior.ColorIndex = 0
Else
Range(cell).Interior.ColorIndex = 6
End If
Application.OnTime Now + 1 / 86400, "doagain"
End Sub
Sub DoAgain()
Application.Run Sheets("Sheet1").CodeName & ".Worksheet_Calculate"
End Sub
I currently reference another cell to determine if I want an existing cell to blink. Example: I want b10 to blink if d10 is >0. I have that working without an issue. However, I have 6 rows of this I'd like to all have the same capability (ex. b11 blinks when d11 > 0 etc.) How to I adjust this code to accomodate each of these rows? Thanks!
Answer Change the worksheet calculate to something like this - I've listed a random-ish set of cells to test & to blink:
Private Sub Worksheet_Calculate()
Dim TestCell As String, BlinkCell As String
For i = 1 To 6
TestCell = Split("B10/D10/E3/E5/C2/D14", "/")(i - 1)
BlinkCell = Split("B8/D11/E13/E12/C7/D3", "/")(i - 1)
If Range(TestCell).Value > 0 Then
Blink BlinkCell
i = 7
Else
Range(BlinkCell).Interior.ColorIndex = 0
End If
Next
End Sub
I set I to 7 to only blink one at a time, otherwise the flashes sort of interfere with the timing since there's a loop which is too fast to see the blinks (remove the i=7 & you'll see what I mean).
However, this is just to give you a start -- it seems like it's not quite doing what I'd expect and I can't dedicate more time to this -- hopefully this will be enough to get you started...
