MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Making Multiple cells Flash in Excel 97


Posted by Steven on January 04, 2001 9:54 AM

I have a macro that allows me to make the active cell flash, but I cannot make multiple cells flash.

Think of it as a grade book for a couple of hundred people.....If a student's grade is an "F" I would like for their grades to flash red. But if there grade is not an "F", I just want it to stay the same.

Please help....


Posted by Celia on January 04, 2001 2:10 PM


Your code is obviously written to flash the active cell only. Try changing your code so that it selects the cells containing "F" (or stores them in a variable) and then run you flashing code.

Celia

Posted by Dave on January 04, 2001 5:20 PM


Hi Steve

This rather annoying bit of code will make a range of cells flash for about 20 secs. But personaly it would get on my nerves.

Dim MyCell As Range
Dim Count As Integer

Sub FlashingRedCells()
Dim MyCells As Range

For Each MyCell In Range("A1:A20")
If MyCell = "F" Then MyCell.Interior.ColorIndex = 46
Next

FlashingWhiteCells
End Sub
Sub FlashingWhiteCells()
Count = Count + 1
For Each MyCell In Range("A1:A20")
If MyCell = "F" Then MyCell.Interior.ColorIndex = xlNone
Next
If Count = 800 Then End
FlashingRedCells
End Sub

Had you looked at Conditional formatting ?


Dave

OzGrid Business Applications

Posted by Celia on January 04, 2001 7:20 PM

Had you looked at Conditional formatting ?

Here's some alternative code. It flashes the "F" cells 7 times and leaves them highlighted in red :-

Sub FlashCells()
Dim toFlash As Range, cell As Range, grades As Range
Dim x%, y%, nbrFlashes%, newClr%, theGrade$
Dim start!, delay!, flashDelay!

Set grades = Range("A1:A20")
theGrade = "F"
newClr = 3
nbrFlashes = 7
flashDelay = 0.15

grades.Interior.ColorIndex = xlNone
For Each cell In grades
If cell.Value = theGrade Then
If x = 0 Then
Set toFlash = cell
x = 1
Else: Set toFlash = Union(toFlash, cell)
End If
End If
Next
Do Until y = nbrFlashes
DoEvents
start = Timer
delay = start + flashDelay
Do Until Timer > delay
DoEvents
toFlash.Interior.ColorIndex = newClr
Loop
start = Timer
delay = start + flashDelay
Do Until Timer > delay
DoEvents
toFlash.Interior.ColorIndex = xlNone
Loop
y = y + 1
Loop
toFlash.Interior.ColorIndex = newClr
End Sub

Celia

Posted by Steven on January 05, 2001 7:01 AM

Thanks for everyone's help so far with this, but what I'm looking for is for the cell to continuously flash for as long as I have the document open.

Here is the code I started using, but it only makes the cell A1 flash and not the rest of them. I've tried manipulating the code to only make certain cells flash (ex. cell that = "F"), but I'm unable to change the range. Please look over the code and see if there is anything that can be changed so I can make multiple cells flash continuously.....

Sub StartFlash()
Dim RunWhen As Double
RunWhen = Now + TimeSerial(0, 0, 1)
Application.OnTime RunWhen, "FlashText"
End Sub
Sub FlashText()
With Range("a1").Interior
If .ColorIndex = xlColorIndexAutomatic Then
.ColorIndex = 3
Else
.ColorIndex = xlColorIndexAutomatic
End If
End With
RunWhen = Now + TimeSerial(0, 0, 1)
Application.OnTime RunWhen, "FlashText"
End Sub
Sub StopFlash()
Application.OnTime RunWhen, "FlashText", , False
End Sub


Posted by Celia on January 05, 2001 8:25 AM

Here is the code I started using, but it only makes the cell A1 flash and not the rest of them. I've tried manipulating the code to only make certain cells flash (ex. cell that = "F"), but I'm unable to change the range. Please look over the code and see if there is anything that can be changed so I can make multiple cells flash continuously..... Sub StartFlash()

Try this :-

Option Explicit
Dim RunWhen As Double
Dim toFlash As Range
Sub StartFlash()
Dim cell As Range, grades As Range
Dim x%, theGrade$

Set grades = Range("A1:A20")
theGrade = "F"

grades.Interior.ColorIndex = xlColorIndexAutomatic
For Each cell In grades
If cell.Value = theGrade Then
If x = 0 Then
Set toFlash = cell
x = 1
Else: Set toFlash = Union(toFlash, cell)
End If
End If
Next
RunWhen = Now + TimeSerial(0, 0, 1)
Application.OnTime RunWhen, "FlashText"
End Sub
Sub FlashText()
With toFlash.Interior
If .ColorIndex = xlColorIndexAutomatic Then
.ColorIndex = 3
Else
.ColorIndex = xlColorIndexAutomatic
End If
End With
RunWhen = Now + TimeSerial(0, 0, 1)
Application.OnTime RunWhen, "FlashText"
End Sub
Sub StopFlash()
Application.OnTime RunWhen, "FlashText", , False
End Sub

Posted by Celia on January 05, 2001 6:50 PM

Sample workbook


Steven
If you send me your address, I will send you a sample workbook which has an improved version of the code (including error handlers and event procedures).
Celia