a hard one

[Chavira55]

can someone help with this?
cell bw43 is a percentage result of a formula(formula is in bw43) how can I make VBA to flash in the result in red or the cell flash in red when it is a positive number?? I've looked at other post, but can not get one to work with what I need.

 

[NCay]

does it have to flash? Or can the positive results just be red, if so look at conditional formatting...

 

[Chavira55]

I like shock value, so I would prefer that it flashes to call the attention of the user

 

[stevebausch]

I will investigate, but I don't think Excel currently has a flash attribute for fonts/colors.

My guess is that the up-and-coming 'XML' formatting will accomodate blinking, just as html does, now.

Until then, I have my doubts that it will be easy to flash text in a worksheet.

Still looking, however.
This message was edited by stevebausch on 2002-10-09 20:31

 

[Colo]

Hi Chavira55,

It does not makes cells red, but makes user pay attention.
Put this code into worksheet module.


<PRE><FONT color=red>Option Explicit</FONT>



<FONT color=red>Private <FONT color=red>Sub </FONT></FONT>Worksheet_Calculate()

<FONT color=red>If </FONT>[BW43].Value >= 0<FONT color=red> Then </FONT><FONT color=red>Call</FONT> Blink([BW43])

<FONT color=red>End Sub</FONT>



<FONT color=red>Private <FONT color=red>Sub </FONT></FONT>Blink(<FONT color=red>ByVal</FONT> rng <FONT color=red>As</FONT> Range)

<FONT color=#666699> 'Thanks for My friend Mr.Hiko
</FONT>
<FONT color=red>Const </FONT>sngInterval <FONT color=red>As</FONT><FONT color=red> Single</FONT> = 0.1 <FONT color=#666699>'Flash Interval
</FONT>
<FONT color=red>Const </FONT>lngTimes <FONT color=red>As</FONT><FONT color=red> Long</FONT> = 3 <FONT color=#666699>'Flash Times
</FONT>
<FONT color=red>Dim </FONT>rngSelected <FONT color=red>As</FONT> Range

<FONT color=red>Dim </FONT>rngPlus <FONT color=red>As</FONT> Range

<FONT color=red>Dim </FONT>lngCnt <FONT color=red>As</FONT><FONT color=red> Long</FONT>

<FONT color=red>Set </FONT>rngSelected = Selection

<FONT color=red>Set </FONT>rngPlus = Union(rng.EntireColumn, rng.EntireRow)

<FONT color=red>For </FONT>lngCnt = 1 <FONT color=red>To </FONT>lngTimes

rngPlus.Select

rng.Activate

myWait sngInterval

rng.Select

myWait sngInterval

Next

rngSelected.Select

<FONT color=red>Set </FONT>rngSelected =<FONT color=red> Nothing</FONT>

<FONT color=red>End Sub</FONT>



<FONT color=red>Private <FONT color=red>Sub </FONT></FONT>myWait(sngInterval <FONT color=red>As</FONT><FONT color=red> Single</FONT>)

<FONT color=red>Dim </FONT>sngTime <FONT color=red>As</FONT><FONT color=red> Single</FONT>

<FONT color=red>Dim </FONT>sngPrev <FONT color=red>As</FONT><FONT color=red> Single</FONT>

sngTime = Timer + sngInterval

Do

sngPrev = Timer

DoEvents

<FONT color=red>Loop</FONT> While sngTime > Timer Or sngPrev >= Timer

<FONT color=red>End Sub</FONT>




</PRE>

 

[Chavira55]

Hello Colo... thanks for the response, but it is not making it do anything... I also tried the next cell (=nextcell) and tied your code to that next one but nothing still. any toughts? I copied the code under thisworkbook

 

[bolo]

Hi there try this. The code works for the cell A1.

Private Sub Worksheet_Calculate()
Dim i As Integer
If [A1].Value >= 0 Then

For i = 1 To 11
Application.OnTime Now + TimeValue("00:00:0" & i), "Flash"
Next i
Else
[A1].Interior.ColorIndex = xlNone
End If

End Sub

Insert a module into the work book and add this code:

Private Sub Flash()

If [A1].Interior.ColorIndex = xlNone Then
[A1].Interior.ColorIndex = 3
GoTo fin
End If
If [A1].Interior.ColorIndex = 3 Then
[A1].Interior.ColorIndex = xlNone
End If
fin:

End Sub

This works for me. HTH