Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
set up a flashing background
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
set up a flashing background
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
set up a flashing background
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
set up a flashing background
Thanks a lot. This should do the trick.
"John Coleman" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Flashing value | Excel Discussion (Misc queries) | |||
Reduce Flashing | Excel Programming | |||
Flashing message in cell (alternate font and background colors) | Excel Programming | |||
Check box text background remains white, does not match general background | Excel Programming | |||
Flashing Cell | Excel Programming |