Can I FLASH an error message on and off?

[Barry Katcher]

I have two numbers in cells A1 and A2. I have a formula in cell A3 that returns either "O.K." or "Discrepancy", depending on whether these two numbers are the same or are not the same.
My question is, can I use conditional formatting or some other function to FLASH the message in A3 on and off, either by switching background colors or text colors or both?
Thanx in advanx.

[zacemmel]

Yah, use the timer control and throw it on the sheet and go from there.

[ This Message was edited by: zacemmel on 2002-09-04 13:46 ]

[Barry Katcher]

Zac, I'm not familiar with the Timer Control. Can you just tell me where to find it and maybe I can figure it out from there. Thanx.

[zacemmel]

Berry,

Goto view - > toolbars - > control toolbox.

On the control toolbox, click the button that says "more controls." From there, goto the Timer control and then draw it on the sheet.

[Joe Was]

These three Sub's will flash the font and/or background color and reset. "Esc" will also stop the code. You can convert the code to a Change Event, then when the cell value changes or is equal to some value the flash code will run. See the code notes for other info.

25 Black 25
26 Magenta 26
27 Yellow 27
28 Cyan 28
29 Violet 29
30 Dark Red 30
31 Teal 31
32 Blue 32
2 White 2
3 Red 3
41 Light Blue 41
11 Dark Blue 11
16 Gray 50% 16
15 Gray 25% 15

Note you can change the rate of flash in the code as well as the color.
Hope this helps. JSW

Sub FlashBack()
'Make cell range Background color, flash x times, x fast, in x color,
'when Ctrl-a is pressed.

Dim newColor As Integer
Dim myCell As Range
Dim x As Integer
Dim fSpeed

'Make this cell range background flash!
Set myCell = Range("A1:A2")
Application.DisplayStatusBar = True
Application.StatusBar = "... Select Cell to Stop and Edit or Wait for Flashing to Stop! "

'Make cell background flash to this color!
'Black 25, Magenta 26, Yellow 27, Cyan 28, Violet 29, Dark Red 30,
'Teal 31, Blue 32, White 2, Red 3, Light Blue 41, Dark Blue 11,
'Gray-50% 16, Gray-25% 15, Bright Cyan 8.
newColor = 27

'Make the cell range flash fast: 0.01 to slow: 0.99
fSpeed = 0.2

'Make cell flash, this many times!
Do Until x = 35

'Run loop!
DoEvents
Start = Timer
Delay = Start + fSpeed
Do Until Timer > Delay
DoEvents
myCell.Interior.ColorIndex = newColor
Loop
Start = Timer
Delay = Start + fSpeed
Do Until Timer > Delay
DoEvents
myCell.Interior.ColorIndex = xlNone
Loop
x = x + 1
Loop
Application.StatusBar = False
Application.DisplayStatusBar = Application.DisplayStatusBar
End Sub

Sub FlashFont()
'Make cell range font flash, x times, x fast, in x color,
'when Ctrl-z is pressed.
Dim newColor As Integer
Dim myCell As Range
Dim x As Integer
Dim fSpeed

'Make this cell range font flash!
Set myCell = Range("A1:A2")
Application.DisplayStatusBar = True
Application.StatusBar = "... Select Cell to Stop and Edit or Wait for Flashing to Stop! "

'Make cell font flash to this color!
'Black 25, Magenta 26, Yellow 27, Cyan 28, Violet 29, Dark Red 30,
'Teal 31, Blue 32, White 2, Red 3, Light Blue 41, Dark Blue 11,
'Gray-50% 16, Gray-25% 15, Bright Cyan 8.

newColor = 3

'Make the cell range flash fast: 0.01 to slow: 0.99
fSpeed = 0.3

'Make cell flash, this many times!
Do Until x = 20

'Run loop!
DoEvents
Start = Timer
Delay = Start + fSpeed
Do Until Timer > Delay
DoEvents
myCell.Font.ColorIndex = newColor
Loop
Start = Timer
Delay = Start + fSpeed
Do Until Timer > Delay
DoEvents
myCell.Font.ColorIndex = xlAutomatic
Loop
x = x + 1
Loop
Application.StatusBar = False
Application.DisplayStatusBar = Application.DisplayStatusBar
End Sub
Sub reSetFlash()
'Re-set cell range color if edit break on color, Ctrl-r to re-set!
ActiveCell.Select
Selection.Interior.ColorIndex = xlNone
End Sub

[Barry Katcher]

Thanx, Zac and Joe - picked up your replies at home, I'll try them tomorrow at work.

[Steve Chyrchel]

Bummer - I followed the yellow brick road, but when I got to the LANDesk time control, drew it on the sheet, I got a message that I can't insert an object. Please advise.

[Steve Chyrchel]

Bummer - I followed the yellow brick road, but when I got to the LANDesk time control, drew it on the sheet, I got a message that I can't insert an object. Please advise.

[Jack in the UK]

Selection.Interior.ColorIndex = 3
Application.Wait (Now + TimeValue("0:00:01"))
Selection.Interior.ColorIndex = 46
Application.Wait (Now + TimeValue("0:00:01"))
Selection.Interior.ColorIndex = 45

[Steve Chyrchel]

Jack,

I really appreciate your help; however, I don't understand VB. I was hoping I could do this with what I knew about Excel.

Steve