MrExcel Publishing
Your One Stop for Excel Tips & Solutions

If cell H21 >$1000.00, blink text??


Posted by Tony on December 14, 2001 5:39 AM

I am tracking the amount of money being spent on cell phone charges for managers in my company. Each person is allowed up to $1000.00 worth of charges each quarter. On my spreadsheet, i would like to have the text, blink in cell H21 if the value is equal to or greater than $1000.00. Is this possible?

Thanks.


Posted by Dan on December 14, 2001 6:41 AM

I modified some code that I found on jwalk.com written by Bill Manville (give credit where credit is due) and that I think this will suit your needs.

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.

Posted by Tony on December 14, 2001 7:42 AM

Dan, thanks it worked great!!! Now, how do i edit the code to also flash if Cell c22 exceeds another number such as 6?
I tried changing the 1000 to a 6 but, it didn't work.

Posted by Tony on December 14, 2001 7:52 AM

Dan,
(1)i am using a spinner to show the values in H21 and H22. When the spinner number reaches 1000, nothing happens. But, if i use my KB to input 1000 in H21, the flashing works. Is there a way to use the spinner numbers?

Posted by Dan on December 14, 2001 9:20 AM

Hmm.. Doesn't seem to want to work using the spin button. I'm taking off here shortly, let me think about it this weekend and get back to you. You can email me (darag2358@yahoo.com) with your e-mail address and I'll get back in touch. Dan,

Posted by Thersites on December 14, 2001 3:50 PM

Instead of the Workbook_SheetChange procedure, use SpinButton1_Change (in the sheet module).

Posted by Dan on December 14, 2001 8:35 PM

Final Answer?

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...

Posted by Thersites on December 14, 2001 10:59 PM

A further final answer ! .............

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) , schedule:=False


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


Posted by Tony on December 17, 2001 6:17 AM

Thanks for the help guy's!!