Help with "Flashing" :)

golf4

Active Member
Joined
Jul 8, 2002
Messages
452
Hi, everyone (again):

You guys have been so much help with my "novice" questions. I really appreciate all the help.

Still working on my income calculation spreadsheet for work, and I was hoping for some real basic tips on how to set flashing cells. I checked out the Dummies book, but nothing. Are there any books that would helps with basic tips on formatting of this type - something like "if A1 = "X", MAKE B2 FLASH RED"?

Again, thanks for the help.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

ylijohe

Board Regular
Joined
Sep 6, 2002
Messages
58
Sub FlashBack()
'Joe Was
'Make cell range Background color, flash x times, x fast, in x color,
'when Ctrl-a is pressed.

Dim newColor As Integer
Dim myCell As Range
Dim x As Integer
Dim fSpeed

'Make this cell range background flash!
Set myCell = Range("A1:M8")
Application.DisplayStatusBar = True
Application.StatusBar = "... Select Cell to Stop and Edit or Wait for Flashing to Stop! "

'Make cell background flash to this color!
'Black 25, 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 = 11

'Make the cell range flash fast: 0.01 to slow: 0.99
fSpeed = 0.2

'Make cell flash, this many times!
Do Until x = 2

'Run loop!
DoEvents
Start = Timer
Delay = Start + fSpeed
Do Until Timer > Delay
DoEvents
myCell.Interior.ColorIndex = newColor
Loop
Start = Timer
Delay = Start + fSpeed
Do Until Timer > Delay
DoEvents
myCell.Interior.ColorIndex = xlNone
Loop
x = x + 1
Loop
Application.StatusBar = False
Application.DisplayStatusBar = Application.DisplayStatusBar
End Sub

Sub FlashFont()
'Joe Was
'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("A1:AF12")
Application.DisplayStatusBar = True
Application.StatusBar = "... Select Cell to Stop and Edit or Wait for Flashing to Stop! "

'Make cell font flash to this color!
'Black 25, 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 = 3

'Make the cell range flash fast: 0.01 to slow: 0.99
fSpeed = 0.3

'Make cell flash, this many times!
Do Until x = 2

'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 = xlAutomatic
Loop
x = x + 1
Loop
Application.StatusBar = False
Application.DisplayStatusBar = Application.DisplayStatusBar
End Sub
Sub reSetFlash()
'Joe Was
'Re-set cell range color if edit break on color, Ctrl-r to re-set!
ActiveCell.Select
Selection.Interior.ColorIndex = xlNone
End Sub

' There´s no such as "dumb question", only dumb question is the one that is not asked.
 

stevebausch

Well-known Member
Joined
May 11, 2002
Messages
810
If this is code you value, make a copy and archive it.

I don't think you will find it anywhere but here at MrExcel. I tried searching on "flash", "blink" etc. and got nowhere.
 

Derek

Well-known Member
Joined
Feb 16, 2002
Messages
1,592

ADVERTISEMENT

Hi there
Here's another variation.

Paste code in a module, type your name in A1 (bold font is better), run macro.


Private Declare Sub Sleep Lib "kernel32" (ByVal dwmilliseconds As Long)
Sub Button1_Click()

Dim x As Integer
Dim y As Integer

Do Until Count = 5
x = 1
Do Until x = Len(Range("A1"))
With Range("A1").Characters(Start:=x, Length:=1).Font
.ColorIndex = 3
End With
Sleep 30
Range("A1").Font.ColorIndex = 2
Sleep 30
x = x + 1
Loop
Count = Count + 1
Range("A1").Font.ColorIndex = xlAutomatic
Sleep 40
Loop
Range("A1").Font.ColorIndex = xlAutomatic

End Sub

regards
Derek
This message was edited by Derek on 2002-10-11 04:19
 

Jack in the UK

Well-known Member
Joined
Feb 16, 2002
Messages
3,215
Hi

Nar this code from Joe Was is guru, and can be played with, he started it off and now its all over the place and versions are to.

I have posted in the past and credit Joe with his work, i use a few edited vessions at work myself

NO it started here from Joe Was, Also Ivan, Colo and a few have added guru touches and all are awesome, plain and simple commet from Jack
 

golf4

Active Member
Joined
Jul 8, 2002
Messages
452

ADVERTISEMENT

Thanks to everyone for their imput on flashing. I've finally figured out, kind of, how to insert the copied code into a workbook. One more question please: if I wanted the now-created macro to activate when, say, cell A1 = "X", how would I construct the formula - something like =if(A1="X",activate macro XXX(?),"").

Thanks again for the help
 
L

Legacy 11273

Guest
Put this in a normal module :-

Dim nextFlash As Date
Sub StartFlash()
nextFlash = Now + TimeValue("00:00:01")
Application.OnTime nextFlash, "StartFlash"
Flash
End Sub

Sub Flash()
With [B1].Font
If [A1] = "X" Then
If .ColorIndex = 3 Then
.ColorIndex = xlAutomatic
Else
.ColorIndex = 3
End If
End If
End With
End Sub

Sub StopFlash()
On Error Resume Next
Application.OnTime nextFlash, "StartFlash", schedule:=False
End Sub


Put this in the ThisWorkbook module :-

Private Sub Workbook_Open()
If [A1].Value = "X" Then Call StartFlash
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
StopFlash
End Sub


If A1 contains a formula put this in the Sheet module :-

Private Sub Worksheet_Calculate()
If [A1].Value = "X" Then
Call StartFlash
Else
Call StopFlash
[B1].Font.ColorIndex = xlAutomatic
End If
End Sub

Else if data in A1 is via the keyboard, put this in the Sheet module :-

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" And [A1] = "X" Then
Call StartFlash
Else
Call StopFlash
[B1].Font.ColorIndex = xlAutomatic
End If
End Sub
 

s-o-s

Active Member
Joined
Apr 14, 2002
Messages
384
On 2002-10-11 00:42, golf4 wrote:
Hi, everyone (again):

You guys have been so much help with my "novice" questions. I really appreciate all the help.

Still working on my income calculation spreadsheet for work, and I was hoping for some real basic tips on how to set flashing cells. I checked out the Dummies book, but nothing. Are there any books that would helps with basic tips on formatting of this type - something like "if A1 = "X", MAKE B2 FLASH RED"?

Again, thanks for the help.

If you are looking for a different approach, this one flashes a named range of cells until you press Ctrl Shift and S together.

This macro will run in the background and enable you to continue working and flashing the cells whilst allowing input.

The process uses the Application.OnTime process to call the macro.

It uses the cells A1:E1 in the first row for variables so you must not use these in your spreadsheet.

Cell A1 is used to tell when to exit the macro.
Cell B1 is the 1st Colour background to use
Cell C1 is the 2nd Colour background to use
(for the above 2 change the cell background colour to the one required)
Cell D1 set to 1 (Manually set this to 1 to start the process. each time the macro runs it alternates this from 1 to NOT 1.)
Cell E1 set this to "00:00:01" (This the timevalue for the delay.)

Last thing you need to do is to define the range of cells that you want to Flash. This can be accomplished by selecting a single cell, a range or seperate cells whilst holding down the Ctrl key.
Next follow Insert | Name | Define give the range the name "RngToFlash" and then click the Add button.

In my version I then added a button on the First Row and assigned this to the FlashCells macro. Then Group row 1 so that it becomes effectively hidden.

Code:
Option Explicit

Sub FlashCells()

Application.ScreenUpdating = False

Dim CurCell As Range
Dim FirstCol, SecndCol, ColToShow As Integer


CurCell = ActiveCell.Address
If [A1] = "Stop" Then Exit Sub

On Error GoTo ErrTrap
    Application.Goto Reference:="RngToFlash"
On Error GoTo 0

Application.OnKey "^+S", "StopFlash"

Application.OnTime Now + [E1], "FlashCells"
    
FirstCol = Range("B1").Interior.ColorIndex
SecndCol = Range("C1").Interior.ColorIndex

If [D1] = 1 Then
    ColToShow = FirstCol
Else
    ColToShow = SecndCol
End If

    Application.Goto Reference:="RngToFlash"

    Selection.Interior.ColorIndex = ColToShow
    
Range(CurCell).Select
[D1] = Not [D1]

GoTo Thend

ErrTrap:

Msg1 = "You have not defined the RngToFlash in Range | Name | Define"
Msg2 = "Define this range then run the macro again"

MsgBox Msg1 & Chr$(10) & Msg2

Thend:
Application.ScreenUpdating = True

End Sub


Sub StopFlash()
Dim CurCell As Range
    
    CurCell = ActiveCell.Address
    Application.Goto Reference:="RngToFlash"
    Selection.Interior.ColorIndex = xlNone
    Range(CurCell).Select
    
    [A1] = "Stop"
    Application.OnKey "^{Esc}", ""
    Application.OnTime Now + [E1] + [E1], "ResetFlash"

End Sub

Sub ResetFlash()

[A1] = ""

End Sub

:)

Repost as something went wrong on the last one.
 

Forum statistics

Threads
1,144,151
Messages
5,722,801
Members
422,459
Latest member
Chriselff

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top