set up a flashing background

[Snake007]

I am trying to make the back ground of a cell flash. I am trying to do this
because I have entered error messages into a cell. I have the cell where it
turns red if there is an error but I would like it to flash to help gain the
attention of the person that has entered incorrect data causing the error.

 

[John Coleman]

Hi

Try something like this:

Sub FlashRed(R As Range, times As Long)
Dim i As Long
Dim start As Double
Dim flashRate As Double 'flashes per second
flashRate = 4
For i = 1 To times
R.Interior.ColorIndex = 3
start = Timer
Do While Timer < start + 1 / (2 * flashRate)
DoEvents
Loop
R.Interior.ColorIndex = xlAutomatic
start = Timer
Do While Timer < start + 1 / (2 * flashRate)
DoEvents
Loop
Next i
R.Interior.ColorIndex = 3
End Sub

Sub test()
FlashRed Range("H3"), 10
End Sub

Adjust flashRate to suit your taste (I guess I could have made it a
function parameter)

HTH

-John Coleman

 

[John Coleman]

In your original post, you didn't specify if you had code displaying
the error message and coloring the cell red or used spreadsheet
formulas and conditional formatting. If the red comes from conditional
formatting, then my code wouldn't work. Here is a version that will:

Sub FlashRedCondition(R As Range, times As Long, condNum As Long)
Dim i As Long
Dim start As Double
Const flashRate = 4 'flashes per second
With R.FormatConditions(condNum).Interior
For i = 1 To times
.ColorIndex = 3
start = Timer
Do While Timer < start + 1 / (2 * flashRate)
DoEvents
Loop
.ColorIndex = xlAutomatic
start = Timer
Do While Timer < start + 1 / (2 * flashRate)
DoEvents
Loop
Next i
.ColorIndex = 3
End With
End Sub

Presumably the cell with the error message is a calculated cell rather
than a data-entry cell (else drawing attention to the cell wouldn't be
a problem and you could use data validation). Hence, is seems
reasonable that the error condition doesn't trigger until something is
recalculated. You can thus capture it with the Worksheet_Calculate()
event:


Private Sub Worksheet_Calculate()
FlashRedCondition Range("H3"), 10, 1
End Sub

Here I have a simple formula in H3 with the first format condition
designed to color the cell red when it is negative. Now, whenever I
change the cell H3 depends on in such a way that H3 becomes negative it
flashes several (10) times before ending up red.

HTH

-John Coleman

 

[Snake007]

Thanks a lot. This should do the trick.

In your original post, you didn't specify if you had code displaying
the error message and coloring the cell red or used spreadsheet
formulas and conditional formatting. If the red comes from conditional
formatting, then my code wouldn't work. Here is a version that will:

Sub FlashRedCondition(R As Range, times As Long, condNum As Long)
Dim i As Long
Dim start As Double
Const flashRate = 4 'flashes per second
With R.FormatConditions(condNum).Interior
For i = 1 To times
.ColorIndex = 3
start = Timer
Do While Timer < start + 1 / (2 * flashRate)
DoEvents
Loop
.ColorIndex = xlAutomatic
start = Timer
Do While Timer < start + 1 / (2 * flashRate)
DoEvents
Loop
Next i
.ColorIndex = 3
End With
End Sub

Presumably the cell with the error message is a calculated cell rather
than a data-entry cell (else drawing attention to the cell wouldn't be
a problem and you could use data validation). Hence, is seems
reasonable that the error condition doesn't trigger until something is
recalculated. You can thus capture it with the Worksheet_Calculate()
event:


Private Sub Worksheet_Calculate()
FlashRedCondition Range("H3"), 10, 1
End Sub

Here I have a simple formula in H3 with the first format condition
designed to color the cell red when it is negative. Now, whenever I
change the cell H3 depends on in such a way that H3 becomes negative it
flashes several (10) times before ending up red.

HTH

-John Coleman