a good one for you Experts.

[flyfishing2542]

I have searched through years of conditional formating posts and VB posts(which I am TOTALLY lost in)

My issue doesnt seem difficult to me... but after days of screaming at my monitor.... /sigh please hel

On my Sheet 1, I have a column that has caculated numbers in it and when that number dips below 50 I want it to blink/flash red.

I have tried this code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = 55 Then
For n = 1 To 10
Target.Interior.Color = vbRed
Delay (0.04)
Target.Interior.ColorIndex = xlNone
Delay (0.04)
Next n
End If
End Sub
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

and EVERY other one I found here and other locations... I have tried just to make them work... which either my excel is too old or I am.

so someone please help...

thank you for your help.

 

[Seti]

Well, a couple of things first.

A Worksheet_Change event won't fire on a change in a calculated cell, so in your case this line of attack won't work. You'll probably need to use a calculate event instead.

Also, you are checking to see if the target value = 55 rather than <50 as your post describes.

I don't have time right now to attack the calculation event though. However, how many cells are in the target column? Is it any cell in this column?

[edit] for typos

 

[mike31z]

You could do the in the past with conditional formatting you just selected a font the had flashing proprieties.
The majority of flashing fonts have been removed due to being able to put a viewer into a seizure from the repetitive flashing.


Mike in wisconsin.

 

[mike31z]

•Blinking text may not be allowed in applications for governmental bodies, since it may be in violation of Section 508 of the Rehabilitation Act Of 1973 (since blinking text can cause seizures in epileptic patients).

here the reference in case.


mike in wisconsin

 

[Michael M]

Hi
I found this some time ago and it allows range ("C16") to flash if the value is less than 100.
Apologies to the original writer, because I can't remember who to give credit to.

Public Sub enterProc()
Application.OnKey "~", "Proc"
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
'Make cell range font flash, x times, x fast, in x color,
'when Ctrl-z is pressed.
Dim newColor As Integer
Dim myCell As Range
Dim x As Integer
Dim fSpeed
'Make this cell range font flash!
Set myCell = Range("C16")
If myCell.Value < 100 Then
myCell.Select
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 18
      End With
'Application.DisplayStatusBar = False
'Application.StatusBar = "... Select Cell to Stop and Edit or Wait for Flashing to Stop! "
'Make cell font flash to this color!
'Black 1, Magenta 26, Yellow 27, Cyan 28, Violet 29, Dark Red 30,
'Teal 31, Blue 32, White 2, Red 3, Light Blue 41, Dark Blue 11,
'Gray-50% 16, Gray-25% 15, Bright Cyan 8.
newColor = 2
'Make the cell range flash fast: 0.01 to slow: 0.99
fSpeed = 0.5
'Make cell flash, this many times!
Do Until x = 5
'Run loop!
DoEvents
Start = Timer
Delay = Start + fSpeed
Do Until Timer > Delay
DoEvents
myCell.Font.ColorIndex = newColor
Loop
Start = Timer
Delay = Start + fSpeed
Do Until Timer > Delay
DoEvents
myCell.Font.ColorIndex = 3
Loop
x = x + 1
Loop
'Application.StatusBar = True
'Application.DisplayStatusBar = Application.DisplayStatusBar
myCell.Select
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Normal"
        .Size = 10
        .ColorIndex = 1
        End With
Else
MsgBox " Minimum Balance achieved, Well Done !!"
End If
End Sub

Make sure the code goes in the worksheet module not "This Workbook"

Regards
Michael M

 

[flyfishing2542]

ok... hmmm. well lets see... I currently have 40 rows in that column.

I took a look around for a font that would be work and I couldnt find any eye poping enough.

I was thinking I could add a column next to the the calculated one.
and if the calculated column was under 50 then I could make BIG red X pop in that column. is that any better?

Please forget that code I posted in the original message. it was just an example of the extreme answers I was looking into.

 

[flyfishing2542]

I tried this code and it wouldnt work for me... just will not run clean.

 

[Michael M]

Waht do you mean by

just will not run clean.

I don't have any problems. Are you putting the code in the Sheet module ??

Regards
Michael M

 

[Michael M]

Also have a look here for more on flashing text

http://www.mrexcel.com/forum/showthread.php?t=372792&highlight=flashing+text

Regards
Michael M

 

[Nokomis449]

If you're willing to go with an eye-catching "X" in an adjacent column, here's a simple fix:
Assume the number is in column "A" and the flag is in column "B":
in cell B1, type =IF(A1<50,"X"," "). Copy this down the 40 columns in "B" so that each cell references the cell beside it (I.E. B2 looks at A2, B3 looks at A3, etc.). Next, highlight column "B", right click on column "B", and click on "Format Cells...". Click on the "Alignment" tab, and in the Horizontal and Vertical dropdown boxes, choose "Center". In the Orientation box, select "-45 Degrees". Click on the "Font" tab and select "Bold Italic" for Font style, select Red fo color, and a Size about two sizes smaller than the original font size (so that the twisted "X" doesn't resize the cell). Click on OK. You should now see an eye-catching flag beside each number that is less than 50.

Jeff