Flashing text and Floating box

[Sourdust]

Sorry to bring this up again but the answer below is exactly what I need except I want cells A:L to flash in a particular row if cell H in that row is blank, otherwise no change.

Also is it possible to have a floating text box with text and then the result of a formula in a cell, eg "Unsetlled Claims (=N2)"?

Thanks for any help and thanks to Phil and Dave for the original solutions.

Sourdust

Q - Can this code be changed so that if cell I54="Quarter End" then Cell F13 blinks? And if it doesn't = "Quarter End" then Cell B13 blinks?



quote:
--------------------------------------------------------------------------------
Originally posted by Drdave1958:
Here is building on Phils code..........


Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("I54").Value = "Quarter End" Then
For n = 1 To 10
Range("F13").Font.Color = vbRed
Delay (0.25)
Range("F13").Font.Color = vbWhite
Delay (0.25)
Next n
ElseIf Range("I54").Value <> "Quarter End" Then
For n = 1 To 10
Range("B13").Font.Color = vbRed
Delay (0.25)
Range("B13").Font.Color = vbWhite
Delay (0.25)
Next n
End If
Range("F13,B13").Font.Color = vbBlack
End Sub



Adjust the numeric value in the "Delay" line to control how long it blinks.

-Dave-

 

[Sourdust]

Hi Jim

I have managed to sneak 5 minutes and have tried adding your code as a macro and just use a control button instead of having it run automatically when the file is opened (I thought that would be easier.) If you get the time, and have the patience, could you tell me what part of the following needs changed to do this or if there is anything else I should do?

Sub Delay(rTime As Single)
'delay rTime seconds (min=.01, max=300)
Dim oldTime As Variant
'safety net
If rTime < 0.01 Or rTime > 300 Then rTime = 1
oldTime = Timer
Do
DoEvents
Loop Until Timer - oldTime > rTime
End Sub

Private Sub Workbook_Open()
On Error Resume Next
'*********************************************
'If you have more than one you need to select it first

'Sheets("Sheet2").Select

'*********************************************

Dim n As Long
For n = 1 To 10
Range("H2:H" & Range("A65536").End(xlUp).Offset(0, 7).Row).SpecialCells(xlCellTypeBlanks).EntireRow.Font.Color = vbRed
Delay (0.25)
Range("H2:H" & Range("A65536").End(xlUp).Offset(0, 7).Row).SpecialCells(xlCellTypeBlanks).EntireRow.Font.Color = vbWhite
Delay (0.25)
Next n
CreateObject("WScript.Shell").Popup "Unsetlled Claims: " & Range("N2").Value, 2, "Wait!"
Range("H2:H" & Range("A65536").End(xlUp).Offset(0, 7).Row).SpecialCells(xlCellTypeBlanks).EntireRow.Font.Color = vbBlack
End Sub