Archive of Mr Excel Message Board

First, copy the following and paste in a Module on your workbook:

Dim NextTime As Date

Sub Flash()
NextTime = Now + TimeValue("00:00:01")
With Cells(21, 8).Font
If .ColorIndex = 2 Then .ColorIndex = 3 Else .ColorIndex = 2
End With
Application.OnTime NextTime, "Flash"
End Sub

Sub StopIt()
Application.OnTime NextTime, "Flash"
Application.OnTime NextTime, "Flash", schedule:=False
Cells(21, 8).Font.ColorIndex = xlAutomatic
End Sub

Next: Copy and paste this code in to the "This Workbook" project sheet:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Calculate
If Cells(21, 8).Value > 1000 Then
Flash
Else
StopIt
End If

End Sub

This should make your cell flash red if over 1000, else black.
Let me know if you need more help. HTH.

Unfortunately it's not that easy. It errors out when trying to constantly change the font of a cell that is linked to a spin button. Here's my way around it. I'm dealing only with cell H21 here and made the code a little more readable. If you need help in adding the other cell, let me know.

First, **remove the cell link from your spin button**. I'm assuming you know how to do that since you put it there in the first place. :)

Then paste the following code in the "This Workbook" code sheet:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
StopItH21
End Sub

Private Sub Workbook_Open()
If Range("H21") > 1000 Then FlashH21 Else StopItH21
End Sub

Then paste the following in the worksheet code that your spin button is on (I am assuming that the spinner for H21 is called SpinButton1:

Private Sub SpinButton1_SpinDown()
Range("H21") = Range("H21") - 1
'Decrements value of cell by 1

If Range("H21") > 1000 Then FlashH21 Else StopItH21
End Sub

Private Sub SpinButton1_SpinUp()
Range("H21") = Range("H21") + 1
'Increments value of cell by 1

If Range("H21") > 1000 Then FlashH21 Else StopItH21

End Sub

Finally, insert a module and place this code there:

Dim NextTime As Date

Sub FlashH21()
NextTime = Now + TimeValue("00:00:02")
With Range("H21").Font
If .ColorIndex = 2 Then .ColorIndex = 3 Else .ColorIndex = 2
End With
Application.OnTime NextTime, "FlashH21"
End Sub

Sub StopItH21()
Application.OnTime NextTime, "FlashH21"
Application.OnTime NextTime, "FlashH21", schedule:=False
Range("H21").Font.ColorIndex = xlAutomatic
End Sub

I tested it and it seems to work. The flashing seems a little quirky (of course I didn't write that part :)) but I can't think of a better way to do that. Let me know if there are any other problems, you have my email address...

You said "It errors out when trying to constantly change the font of a cell that is linked to a spin button". I couldn't produce any error.

The following works for me :-

Dim NextTime As Date

Sub Flash()
NextTime = Now + TimeValue("00:00:01")
With Cells(21, 8).Font
If .ColorIndex = 2 Then .ColorIndex = 3 Else .ColorIndex = 2
End With
Application.OnTime NextTime, "Flash"
End Sub

Sub StopIt()
Application.OnTime NextTime, "Flash"
Application.OnTime NextTime, "Flash", schedule:=False
Cells(21, 8).Font.ColorIndex = xlAutomatic
End Sub

Private Sub SpinButton1_Change()
StopIt
If Cells(21, 8).Value > 6 Then Flash
End Sub

Private Sub Workbook_Open()
If Cells(21, 8).Value > 6 Then Flash
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
StopIt
End Sub