John Coleman
12/18/2006 5:34:00 PM
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
John Coleman wrote:
> 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
>
> Snake007 wrote:
> > 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.